sum all rows with multiple matching criteria
 

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

sum all rows with multiple matching criteria
 

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




"The Fru Fru" wrote in message
...
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

sum all rows with multiple matching criteria
 

On the face of this line:
.. 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
---
"The Fru Fru" wrote:
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

sum all rows with multiple matching criteria
 

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

sum all rows with multiple matching criteria
 

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

"Max" wrote:

On the face of this line:
.. 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
---
"The Fru Fru" wrote:
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

sum all rows with multiple matching criteria
 

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
---
"The Fru Fru" wrote in message
...
Beauty, works a treat, i'm impressed, now to impress the boss!