Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum rows if two columns = specific criteria.
I would like to set up a formula that will allow me to sum a column if the
row meets two criteria. A B C D 1 Apple WA Active 5 2 Pear OR Closed 6 3 Apple OR Active 2 4 cherry WA Closed 3 5 cherry ID Active 4 6 Peach ID Closed 7 example, I would like the sum of Column D where colum B = Apple & column C = Wa in this example that would be 5 I have been unable to figure this out using two variables. -- Jeff |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum rows if two columns = specific criteria.
=SUMPRODUCT(--(A1:A100="Apple),--(B1:B100="Wa"))
Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I would like to set up a formula that will allow me to sum a column if the row meets two criteria. A B C D 1 Apple WA Active 5 2 Pear OR Closed 6 3 Apple OR Active 2 4 cherry WA Closed 3 5 cherry ID Active 4 6 Peach ID Closed 7 example, I would like the sum of Column D where colum B = Apple & column C = Wa in this example that would be 5 I have been unable to figure this out using two variables. -- Jeff |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum rows if two columns = specific criteria.
Hi,
You may also try the following array formula (Ctrl+Shift+Enter): =sum(if((rangeA="Apple")*(rangeB="WA"),rangeD)) Regards, "Jeff" wrote: I would like to set up a formula that will allow me to sum a column if the row meets two criteria. A B C D 1 Apple WA Active 5 2 Pear OR Closed 6 3 Apple OR Active 2 4 cherry WA Closed 3 5 cherry ID Active 4 6 Peach ID Closed 7 example, I would like the sum of Column D where colum B = Apple & column C = Wa in this example that would be 5 I have been unable to figure this out using two variables. -- Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
Hiding of rows and columns | Excel Discussion (Misc queries) | |||
averaging specific rows in multiple arrays | Excel Worksheet Functions | |||
Display specific rows from table on other worksheet | Excel Discussion (Misc queries) |