Adding contents of cells based on month and location

    Hi folks


    I'm trying to get the contents of cells added together based on the month and location that refer to those cells using:


    =SUMPRODUCT(TEXT(Weekly_Attend!C3:L3,"mmm")=TEXT(Dashboard!D17,"mmm"),(Weekly_Attend!B4:B7)=Dashboard!C17,Weekly_Attend!C4:L7)


    However, it keeps returning a #VALUE error, any ideas please?? :)


    Count-by-Month-and-Locationxlsx.xlsx


    I've attached a sample workbook so that it can be seen how I'm trying to get it to work.


    Any help really appreciated please...


    Kind regards


    DezB

    I've also tried:


    =SUMPRODUCT((Weekly_Attend!B4:B7=Dashboard!C17),(TEXT(Weekly_Attend!C3:L3,"mmm")=TEXT(Dashboard!D17,"mmm")*Weekly_Attend!C4:L7))


    But getting the same #VALUE! error... :(


    Kind regards


    DezB

    An alternative is with Power Query


    Code
    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
#"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Attribute]), Int64.Type),
#"Grouped Rows" = Table.Group(#"Inserted Month", {"Location", "Month"}, {{"Attendees", each List.Sum([Value]), type number}})
in
#"Grouped Rows"

    Hi Alan


    Thank you for the reply, much appreciated.


    Yes, I thought about it, but unfortunately the workbook will be stored in a Sharepoint folder that often prevents macros from working. It's a real pain in the proverbial.


    Kind regards


    Deryn

    What I provided you is not a Macro. It is Power Query which is native to Excel and found on the Data Tab. In your version it is called Get and Transform Data. Here is some more info on PQ


    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.
    - Follow this link for an introduction to Power Query functionality.
    - Follow this link for a video which demonstrates how to use Power Query code provided.

    Hi Alan


    Thank you for the update, every day is a school day it seems. I shall take a good look, thank you... :)


    Kind regards


    Deryn

    Hi Alan.


    Well, there's certainly a lot of learning to do which is interesting but, in the meantime, if anyone can sort that formulae to make it work I'd certainly appreciate it.


    Cheers


    Deryn

    Hi Pecoflyer


    Fantastic, thank you, got me out of a hole and I can now move on to try and learn some more about Power Queries.


    Much appreciated.


    Kind regards


    Deryn

    I put the data set on the same page as the criteria for ease of writing formula.

    This sumproduct formula worked for me.

    =SUMPRODUCT((C4:C7=C17)*(TEXT(D3:M3,"mmm")=D17)*D4:M7)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login