rFor example: SUMIF(B2:B7, "Apple OR Orange", C2:C7) If it's an apple or an
orange, sum their values in column c.
It does not work. Can anyone help?

=SUMPRODUCT(--(((B2:B7="apple")+(B2:B7="orange"))0),C2:C7)


"iampritzy" wrote:

rFor example: SUMIF(B2:B7, "Apple OR Orange", C2:C7) If it's an apple or an
orange, sum their values in column c.
It does not work. Can anyone help?

As long as the conditions are disjoint (they can't both be satisfied as the
same time), just add the results of multiple sumif's:
=sumif(b2:b7,"Apple",c2:c7)+sumif(b2:b7,"Orange",c 2:c7)

"iampritzy" wrote:

rFor example: SUMIF(B2:B7, "Apple OR Orange", C2:C7) If it's an apple or an
orange, sum their values in column c.
It does not work. Can anyone help?

Long intuitive version..

=SUMPRODUCT(SUMIF(B2:B7, {"Apple","Orange"},C2:C7))

...slightly shorter but less intuitive.

=SUMPRODUCT((B2:B7={"Apple","Orange"})*C2:C7)

Hard coding the sum up values should be avoided.
You could put them into a cell range, in this case J1 and K1, one variable per cell.

=SUMPRODUCT((B2:B7=J1:K1)*C2:C7)

Use J1:J2 and it wont work, the range size having to be the same and all that, but for some reason it don't apply if it goes in another direction, or for Sumif - go figure.

=SUMPRODUCT(SUMIF(B2:B17,J1:J2,C2:C7))


Regards
Robert McCurdy

"iampritzy" wrote in message ...
rFor example: SUMIF(B2:B7, "Apple OR Orange", C2:C7) If it's an apple or an
orange, sum their values in column c.
It does not work. Can anyone help?

I tried all solutions you guys provided. Thank you all.

"EvolBob" wrote:

Long intuitive version..

=SUMPRODUCT(SUMIF(B2:B7, {"Apple","Orange"},C2:C7))

...slightly shorter but less intuitive.

=SUMPRODUCT((B2:B7={"Apple","Orange"})*C2:C7)

Hard coding the sum up values should be avoided.
You could put them into a cell range, in this case J1 and K1, one variable per cell.

=SUMPRODUCT((B2:B7=J1:K1)*C2:C7)

Use J1:J2 and it wont work, the range size having to be the same and all that, but for some reason it don't apply if it goes in another direction, or for Sumif - go figure.

=SUMPRODUCT(SUMIF(B2:B17,J1:J2,C2:C7))


Regards
Robert McCurdy

"iampritzy" wrote in message ...
rFor example: SUMIF(B2:B7, "Apple OR Orange", C2:C7) If it's an apple or an
orange, sum their values in column c.
It does not work. Can anyone help?

surely simpler.....

=SUM(SUMIF(B2:B7,{"Apple","Orange"},C2:C7))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=526488