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:


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


    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


  • I've also tried:


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

    Kind regards


  • An alternative is with Power Query

    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}})
    #"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


  • 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


  • 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.



  • 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


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

    This sumproduct formula worked for me.


Participate now!

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