sum all rows with multiple matching criteria

[The Fru Fru]

I have a spreadsheet with all our invoice and account code info on it.
Month Cost Site Code GL Code sub acct
3 $75.00 700441 5790 020
1 $735.00 700441 5790 020
7 $74.00 704200 5790 50
I have a budget spreadsheet with month along the columns and the account
details down the rows
JAN FEB MAR

5790 FACILITIES
020 Hazardous waste
50 Rubbish disposal
Other
Total 5790 FACILITIES 0 0 0

I am looking for a formula that will look for and sum up all the $ values
for month 1 (Jan) that match code 700441 and 5790 and 020.

Thanks

 

[Paul Wilson]

If you have XL2007 have a look at the SUMIFS function otherwise in earlier
versions one option is the SUMPRODUCT function.

Using your sample data I used the following to get the result.

=SUMPRODUCT((Month=1)*(Site_Code=700441)*(GL_Code=5790)*(sub_acct=20)*(Cost))

I created range names for the data (rows) by selecting the list then from
the menu INSERT>NAME>CREATE and ensured the Top Row option was checked. It
makes the formula more intuitive rather than (A2:A4=1)*(C2:C4=700441)...
etc.

There is a really useful article on this link if you want the full info on
this function.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

The excel help information on this function is crap.

Regards

Paul

 

[Max]

.. a formula that will look for and sum up all the $ values
for month 1 (Jan) that match code 700441 and 5790 and 020

Assume source table as posted is in sheet: x,
cols A to E, data from row2 down,
where cols A and B contains real nums,
while cols C to E contains text nums

then this expression should return it for you:
=SUMPRODUCT((x!A2:A10=1)*(x!C2:C10="700441")*(x!D210="5790")*(x!E2:E10="020"),x!B2:B10)

Adapt the ranges to suit

Drop the double quotes if cols C to E contains real nums,
eg: ="700441" becomes just: =700441
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Herbert Seidenberg]

Excel 2007
Pivot Table
No formulas needed.
Free extras: Chart, Subtotals, Filters, Colors
http://www.mediafire.com/file/n2zm2yqofg5/02_05_09.xlsx

 

[The Fru Fru]

Beauty, works a treat, i'm impressed, now to impress the boss!

 

[Max]

Glad to hear. Once you get the hang of it, it's a breeze.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik