Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Nested IF
Hi,
I want to do a SUMIF but on more than one condition. For example: A B C 3 Apple Green 4 Orange Blue 4 Pear Yellow 5 Orange Blue 6 Orange Red I want to sum the amounts in column A, based on column B and C. In this case, sum the amounts in column A for Oranges in column B and Blue in Column C. Any help would be appreciated. James |
#2
|
|||
|
|||
Hi James
One way =SUMPRODUCT(--($B$2:$B$5="Oranges"),--($C$2:$C$5="Blue"),$A$1:$A$5) Regards Roger Govier James Hamilton wrote: Hi, I want to do a SUMIF but on more than one condition. For example: A B C 3 Apple Green 4 Orange Blue 4 Pear Yellow 5 Orange Blue 6 Orange Red I want to sum the amounts in column A, based on column B and C. In this case, sum the amounts in column A for Oranges in column B and Blue in Column C. Any help would be appreciated. James |
#3
|
|||
|
|||
Sum product is not working..... coming up with "0".
Any other ideas? "Roger Govier" wrote: Hi James One way =SUMPRODUCT(--($B$2:$B$5="Oranges"),--($C$2:$C$5="Blue"),$A$1:$A$5) Regards Roger Govier James Hamilton wrote: Hi, I want to do a SUMIF but on more than one condition. For example: A B C 3 Apple Green 4 Orange Blue 4 Pear Yellow 5 Orange Blue 6 Orange Red I want to sum the amounts in column A, based on column B and C. In this case, sum the amounts in column A for Oranges in column B and Blue in Column C. Any help would be appreciated. James |
#4
|
|||
|
|||
I think Roger meant:
=SUMPRODUCT(--($B$1:$B$5="Orange"),--($C$1:$C$5="Blue"),$A$1:$A$5) -- Jim "James Hamilton" wrote in message ... | Sum product is not working..... coming up with "0". | | Any other ideas? | | "Roger Govier" wrote: | | Hi James | | One way | =SUMPRODUCT(--($B$2:$B$5="Oranges"),--($C$2:$C$5="Blue"),$A$1:$A$5) | | Regards | | Roger Govier | | | James Hamilton wrote: | Hi, | | I want to do a SUMIF but on more than one condition. For example: | | A B C | 3 Apple Green | 4 Orange Blue | 4 Pear Yellow | 5 Orange Blue | 6 Orange Red | | I want to sum the amounts in column A, based on column B and C. In this | case, sum the amounts in column A for Oranges in column B and Blue in Column | C. | | Any help would be appreciated. | | James | | |
#5
|
|||
|
|||
Apologies James
I mistyped. It should be =SUMPRODUCT(--($B$2:$B$5="Orange"),--($C$2:$C$5="Blue"),$A$2:$A$5) Ranges must be of equal size in sumproduct. I typed a 1 instead of 2 for the range in column A and I typed "Oranges" instead of "Orange" for the criterion in column B. Must be time to get the coffee pot brewing again!!! Regards Roger Govier James Hamilton wrote: Sum product is not working..... coming up with "0". Any other ideas? "Roger Govier" wrote: Hi James One way =SUMPRODUCT(--($B$2:$B$5="Oranges"),--($C$2:$C$5="Blue"),$A$1:$A$5) Regards Roger Govier James Hamilton wrote: Hi, I want to do a SUMIF but on more than one condition. For example: A B C 3 Apple Green 4 Orange Blue 4 Pear Yellow 5 Orange Blue 6 Orange Red I want to sum the amounts in column A, based on column B and C. In this case, sum the amounts in column A for Oranges in column B and Blue in Column C. Any help would be appreciated. James |
#7
|
|||
|
|||
Hi James
" O ye of little faith ....!!!!" Try it and see. If the data is as you say, then the formula given will return the value 9. Regards Roger Govier James Hamilton wrote: Hi, I picked up the mistake with the "orange" vs "oranges" ...... and I looked up the sumproduct function at work today, and it appears to be a multiplication function based on arrays. I want to SUM a column based on a SUMIF of two columns - not sure if SUMPRODUCT would do this? Thanks - "Roger Govier" wrote: Apologies James I mistyped. It should be =SUMPRODUCT(--($B$2:$B$5="Orange"),--($C$2:$C$5="Blue"),$A$2:$A$5) Ranges must be of equal size in sumproduct. I typed a 1 instead of 2 for the range in column A and I typed "Oranges" instead of "Orange" for the criterion in column B. Must be time to get the coffee pot brewing again!!! Regards Roger Govier James Hamilton wrote: Sum product is not working..... coming up with "0". Any other ideas? "Roger Govier" wrote: Hi James One way =SUMPRODUCT(--($B$2:$B$5="Oranges"),--($C$2:$C$5="Blue"),$A$1:$A$5) Regards Roger Govier James Hamilton wrote: Hi, I want to do a SUMIF but on more than one condition. For example: A B C 3 Apple Green 4 Orange Blue 4 Pear Yellow 5 Orange Blue 6 Orange Red I want to sum the amounts in column A, based on column B and C. In this case, sum the amounts in column A for Oranges in column B and Blue in Column C. Any help would be appreciated. James |
#8
|
|||
|
|||
what about =sumif()+sumif()
-- paul remove nospam for email addy! "James Hamilton" wrote: Hi, I want to do a SUMIF but on more than one condition. For example: A B C 3 Apple Green 4 Orange Blue 4 Pear Yellow 5 Orange Blue 6 Orange Red I want to sum the amounts in column A, based on column B and C. In this case, sum the amounts in column A for Oranges in column B and Blue in Column C. Any help would be appreciated. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Subtotals in Excel 2002 | Excel Discussion (Misc queries) | |||
Why are my nested sub-totals are displaying incorrectly? | Excel Discussion (Misc queries) | |||
Nested IF statements | Excel Worksheet Functions | |||
€śUse of more then 7 nested if statement€ť | Excel Worksheet Functions | |||
nested ifs | Setting up and Configuration of Excel |