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

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

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

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

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

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

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

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

Hi Roger,

Thanks again - I'll try it again at work and lete you know how it goes.

By the way, I added the "conditional sum" add in today, and that makes it
work and uses the { bracket at the start and end of the formula - never seen
that before. The only thing with that is that you can't amend the formula,
it's a strange one.

I would have thought that MS would have a function that allows more than one
condition in the SUMIF function.

Thanks,

James

"Roger Govier" wrote:

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

Roger,

It worked a treat - many thanks.

Hey, what's with the -- in the formula?

James

"Roger Govier" wrote:

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

Hi James.

Glad it worked for you. Thanks for the feedback.

The -- (double unary minus) is used to coerce the True/False results into
1's and 0's.

So in the first part of the formula the False, True, False, True, True
result from testing whether the value in cells B2:B5 = "Orange"
get changed to 0,1,0,1,1. The second part becomes 0,1,0,1,0.
So with 3,4,4,5,6 as your values in column A we get
0*0*3 =0
1*1*4 =4
0*0*4 =0
1*1*5 =5
1*0*6 =0

which get summed to give your result of 9.


Regards

Roger Govier


James Hamilton wrote:
Roger,

It worked a treat - many thanks.

Hey, what's with the -- in the formula?

James

"Roger Govier" wrote:


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

Hi Roger,

Thanks for taking the time to explain this.

I mucked around with the formula a bit more and got it to work without the
doubly unary minus signs (hyphens to me!).

Although a different example, my formula is
=SUMPRODUCT(($B$2:$B$7="Orange")*($C$2:$C$7="Blue" )*(D2:D7="yes"),$A$2:$A$7)

and so it looks for "orange" in column B, "blue" in column C and "yes" in
column D, then sums the relevant numbers in column A

Is this incorrect; do yuo have to use the double unary minus signs?

Thanks again,

james DOT hamilton AT optusnet DOT COM DOT AU



"Roger Govier" wrote:

Hi James.

Glad it worked for you. Thanks for the feedback.

The -- (double unary minus) is used to coerce the True/False results into
1's and 0's.

So in the first part of the formula the False, True, False, True, True
result from testing whether the value in cells B2:B5 = "Orange"
get changed to 0,1,0,1,1. The second part becomes 0,1,0,1,0.
So with 3,4,4,5,6 as your values in column A we get
0*0*3 =0
1*1*4 =4
0*0*4 =0
1*1*5 =5
1*0*6 =0

which get summed to give your result of 9.


Regards

Roger Govier


James Hamilton wrote:
Roger,

It worked a treat - many thanks.

Hey, what's with the -- in the formula?

James

"Roger Govier" wrote:


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

Hi James

Glad you are sorted.
No you don't have to use the double unary. I always used to use the "*" ( or
"+" if you want OR in stead of AND) but I was guided by a very good treatise
on Sumproduct by Bob Phillips.

Take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html
It is a fairly long article, but well worth the read.


Regards

Roger Govier


James Hamilton wrote:
Hi Roger,

Thanks for taking the time to explain this.

I mucked around with the formula a bit more and got it to work without the
doubly unary minus signs (hyphens to me!).

Although a different example, my formula is
=SUMPRODUCT(($B$2:$B$7="Orange")*($C$2:$C$7="Blue" )*(D2:D7="yes"),$A$2:$A$7)

and so it looks for "orange" in column B, "blue" in column C and "yes" in
column D, then sums the relevant numbers in column A

Is this incorrect; do yuo have to use the double unary minus signs?

Thanks again,

james DOT hamilton AT optusnet DOT COM DOT AU



"Roger Govier" wrote:


Hi James.

Glad it worked for you. Thanks for the feedback.

The -- (double unary minus) is used to coerce the True/False results into
1's and 0's.

So in the first part of the formula the False, True, False, True, True
result from testing whether the value in cells B2:B5 = "Orange"
get changed to 0,1,0,1,1. The second part becomes 0,1,0,1,0.
So with 3,4,4,5,6 as your values in column A we get
0*0*3 =0
1*1*4 =4
0*0*4 =0
1*1*5 =5
1*0*6 =0

which get summed to give your result of 9.


Regards

Roger Govier


James Hamilton wrote:

Roger,

It worked a treat - many thanks.

Hey, what's with the -- in the formula?

James

"Roger Govier" wrote:



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