Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAX value matching multiple criteria | Excel Worksheet Functions | |||
VLookup - Multiple Rows with Exact Matching Column 1 | Excel Discussion (Misc queries) | |||
Sorting and Matching criteria across rows to return a result in the last cell | Excel Worksheet Functions | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions |