Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple SUMIF criteria
Imagine the following columns:
A is the Project# B is the Cost Type (Exp or Cap) and C through M (a column for each month of the year) that contains the invoice amount received. There are 200 rows of data I would like to add up all of the Capital invoices for a specific project...can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple SUMIF criteria
SUMIF is fine if you only have one criterion, but if you have two or
more then you can use SUMPRODUCT. Bob Phillips shows how he http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps. Pete On Oct 9, 9:11*pm, Markl9869 wrote: Imagine the following columns: A is the Project# B is the Cost Type (Exp or Cap) and C through M (a column for each month of the year) that contains the invoice amount received. There are 200 rows of data I would like to add up all of the Capital invoices for a specific project...can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple SUMIF criteria
I hope you are not the one who wants everything in one formula.
Assuming your amounts are in Col C-N (C-M will give you 11 cells only) enter this in Col O and copy down =SUM(C1:N1) Enter this anywhere (other than Cols A, B and O); =SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Cap"),(O1:O100)) Replace A with your project#. "Markl9869" wrote: Imagine the following columns: A is the Project# B is the Cost Type (Exp or Cap) and C through M (a column for each month of the year) that contains the invoice amount received. There are 200 rows of data I would like to add up all of the Capital invoices for a specific project...can anyone help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple SUMIF criteria
This seems to work if I do the function on the same worksheet but I can't
seem to get it to work if i reference the data from another worksheet... "Sheeloo" wrote: I hope you are not the one who wants everything in one formula. Assuming your amounts are in Col C-N (C-M will give you 11 cells only) enter this in Col O and copy down =SUM(C1:N1) Enter this anywhere (other than Cols A, B and O); =SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Cap"),(O1:O100)) Replace A with your project#. "Markl9869" wrote: Imagine the following columns: A is the Project# B is the Cost Type (Exp or Cap) and C through M (a column for each month of the year) that contains the invoice amount received. There are 200 rows of data I would like to add up all of the Capital invoices for a specific project...can anyone help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple SUMIF criteria
Assuming your data is in Sheet2 then replace A1:A100 with Sheet2!A1:A100 and
so on for other columns... You can also type the formula to =SUMPRODUCT(--( then go to sheet2 and select the range in Col A then type ="A"),--( and select range in Col B and so on... "Markl9869" wrote: This seems to work if I do the function on the same worksheet but I can't seem to get it to work if i reference the data from another worksheet... "Sheeloo" wrote: I hope you are not the one who wants everything in one formula. Assuming your amounts are in Col C-N (C-M will give you 11 cells only) enter this in Col O and copy down =SUM(C1:N1) Enter this anywhere (other than Cols A, B and O); =SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Cap"),(O1:O100)) Replace A with your project#. "Markl9869" wrote: Imagine the following columns: A is the Project# B is the Cost Type (Exp or Cap) and C through M (a column for each month of the year) that contains the invoice amount received. There are 200 rows of data I would like to add up all of the Capital invoices for a specific project...can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
SUMIF with multiple criteria | Excel Discussion (Misc queries) | |||
Sumif with multiple criteria | Excel Discussion (Misc queries) | |||
SumIf with Multiple Criteria | Excel Worksheet Functions | |||
SUMIF, multiple criteria | Excel Discussion (Misc queries) |