# sum all rows with multiple matching criteria

T

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

P

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

M

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

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

T

#### The Fru Fru

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

M