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
 
Ad

Advertisements

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!D2:D10="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
---
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top