• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Total Amounts from Horizontal Data to Vertical Table

YPJunk

Member
Hello Everyone,

I hope you are well. For my group we need to track RSVPs and Attendance for each event type per date. I would like to have a summary of the data where it should total attendance and RSVPs for each event type. I tried the sumifs function but was unsuccessful. Any insight you have would be greatly appreciated. Please see attached file. Thank you!

Sincerely,

Yvette
 

Attachments

  • Test Tracker.xlsx
    40.1 KB · Views: 12
Hi
please note that using jargon is not understood by everyone. To me RSVP is a mystery and I don't want to waste time searching for it
This being said, your worksheet design could be much better for analysis
First of all using merged cells is a very bad idea, they are nothing but trouble
Second, why not build a simple table with columns headed Date - BWB - RSVP -Attendance - Guest/Note
From there you can easily create any analysis you want ( like a Pivot Table) and it's even auto updating using the Excel Table feature
 
@YPJunk, some solid advice from @pecoflyer...

@pecoflyer , allow me to suggest a small clarification here
From there you can easily create any analysis you want ( like a Pivot Table) and it's even auto updating using the Excel Table feature
The pivot's data source is auto updated, simply performing a refresh will update the pivot including any new data that comes in. I know, you know, but the OP might not. ;-)

@YPJunk , if the data source is what it is, and you have Excel 2010 or later, then maybe a simple (all done by the UI) Power Query* solution is possible:
Rich (BB code):
let
    Source = Excel.Workbook(File.Contents("G:\Uploads\Test Tracker.xlsx"), null, true),
    #"Event Data Details_Sheet" = Source{[Item="Event Data Details",Kind="Sheet"]}[Data],
    #"Filtered Rows" = Table.SelectRows(#"Event Data Details_Sheet", each [Column2] <> null or [Column4] = "Date -->"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1", "Column4"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filled Down", {{"Column1", type text}}, "nl-BE"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Labels"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"|BWB Event Type", "|Guest/Note"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "Date"}, {"|Guest/Note", "Guest/Note"}, {"|BWB Event Type", "BWB Event Type"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Attendance", Int64.Type}, {"RSVP", Int64.Type}, {"Date", type date}, {"Guest/Note", type text}, {"BWB Event Type", type text}})
in
    #"Changed Type"

* the "source" step must be updated, click the gear icon in the applied steps section at the right. Navigate to your file file.
 

Attachments

  • Test Tracker.xlsx
    51 KB · Views: 8
Thank you. Excuse the jargon.
Np, when posting just put yourself in one of our members shoes. they're all great at XL, that we know for a fact, but not necessarily savvy in your area of expertise.
@GraH - Guido
I'd love to learn that PQ business, but XL working on Ubuntu does not allow it :rolleyes:
 
A brute-force approach using 2D helper ranges to mask the data to match the criteria.
Code:
= IF(ISTEXT(EventResponse), EventType)
= IF(ISTEXT(EventType), EventResponse)

with the formula

= SUMIFS(EventData,
    MaskType, SummaryType,
    MaskResponse, SummaryResponse)
The helper ranges are needed because the IFS family of functions expect range references and not arrays.
 

Attachments

  • Event Tracker (PB).xlsx
    109.6 KB · Views: 1
RSVP: from French "répondez s'il vous plaît"; please reply (used at the end of invitations to request a response)
Apparently obsolete in France but understood in Canada.
 
I have just realised; I can do away with the helper ranges used to filter the event data and, instead, use a couple of matrix multiplications for that purpose.
Code:
= LET(
  EventFilter, SIGN(SummaryResponse = TRANSPOSE(EventResponse)),
  TypeFilter, SIGN(SummaryType=TRANSPOSE(EventType)),
  MMULT(TypeFilter, MMULT(IF(EventData>0, EventData, 0), EventFilter)) )
Without MS 365, EventFilter and TypeFilter would be defined Names and the nested MMULTS, that form the product of the three arrays, would be the worksheet formula.
 
Back
Top