yogi_Create A Monthly OverView Sheet From An Expense Database Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #638   Jul 18, 2012     www.energyefficientbuild.com.


user Trojkolka said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/q7MBn_LUDgI)
Is there a way to match a list of criteria in this SUM formula
After making this work. I was thinking about the maintainability of the spreadsheet.

I have a list of shops in the database and if those shops are found it should sum the money spent at that shop for that particular month.

The following formula does this well:

=ArrayFormula(sum(((MONTH(Database!$A$2:$A)=$B2)*(YEAR(Database!$A$2:$A)=$A2)*(Database!$C$2:$C="Shop 1")*Database!$B$2:$B))+sum(((MONTH(Database!$A$2:$A)=$B2)*(YEAR(Database!$A$2:$A)=$A2)*(Database!$C$2:$C="Shop 2")*Database!$B$2:$B))+sum(((MONTH(Database!$A$2:$A)=$B2)*(YEAR(Database!$A$2:$A)=$A2)*(Database!$C$2:$C="Shop 4")*Database!$B$2:$B))+sum(((MONTH(Database!$A$2:$A)=$B2)*(YEAR(Database!$A$2:$A)=$A2)*(Database!$C$2:$C="Shop 5")*Database!$B$2:$B))+sum(((MONTH(Database!$A$2:$A)=$B2)*(YEAR(Database!$A$2:$A)=$A2)*(Database!$C$2:$C="Another Shop here")*Database!$B$2:$B)))

But there has to be a smarter and better way than for every shop to add the whole "+sum((...)))" part. Is it possible to make a list of shop names to search for and let this formula use that list instead. Something like:

=ArrayFormula(sum(((MONTH(Database!$A$2:$A)=$B2)*(YEAR(Database!$A$2:$A)=$A2)*(Database!$C$2:$C=INARRAY("SHOPS"))*Database!$B$2:$B)))

Obviously the above mentioned formula doesn't work so I would really appreciate your ideas on how to make it possible.

I prepared a quick spreadsheet where the formula is in use here: https://docs.google.com/spreadsheet/ccc?key=0AgXiTbpCBWqqdFlfMlhwZTB3YmF0Z3JVS0FSeEZvTmc

The formula is in row F2:F13 on the second sheet called "Montly Overview". Data comes from first tab called "Database".

Hope someone can help me with this one.

Thanks in advance,

Trojkolka

----------------------------------------------------------------------------------------------------------

I present the following solution based on my understanding of what Trojkolka wants to accomplish

Spreadsheet settings ... Locale: Finland