## Wednesday, July 18, 2012

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

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.