• 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

New 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

pecoflyer

Active Member
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
 

GraH - Guido

Well-Known Member
@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

pecoflyer

Active Member
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:
 

Peter Bartholomew

Well-Known Member
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

Peter Bartholomew

Well-Known Member
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.
 

Peter Bartholomew

Well-Known Member
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.
 
Top