Sumproduct Problem

[RAF1112]

Hello,

I am trying to use a sumproduct to do a mutliconditional sumif. One of the array has numbers and text so I keep getting a #Value error. Is there any way to only do the sumproduct on the rows with numbers?

Thanks!

 

[Aladin Akyurek]

Hello,

I am trying to use a sumproduct to do a mutliconditional sumif. One of the array has numbers and text so I keep getting a #Value error. Is there any way to only do the sumproduct on the rows with numbers?

Thanks!

Try to describe the problem to solve, preferably along with a tiny sample and desired result(s).

 

[RAF1112]

Example...sorry

here is a quick example and some elaboration on my problem. I am using the equation:

=sumproduct((a1:a5>0)*(b1:b5="N")*(c1:c5))

c1, c2, and c5 have numbers. C3 and c4 have text so I get a #value error.

Is there anyway to make this work?

 

[Brian from Maui]

Re: Example...sorry

here is a quick example and some elaboration on my problem. I am using the equation:

=sumproduct((a1:a5>0)*(b1:b5="N")*(c1:c5))

c1, c2, and c5 have numbers. C3 and c4 have text so I get a #value error.

Is there anyway to make this work?

If you have text in the range to Sum, switch to the comma syntax. If you literally have the #VALUE error, you'll have to use something else.

 

[RAF1112]

Darn

Th comma syntax didn't work either and I don't have #value in the selected range. Thanks for trying!

 

[Seti]

Can you add another column to evaluate column C using N(C1), etc?

 

[Brian from Maui]

Re: Darn

Th comma syntax didn't work either and I don't have #value in the selected range. Thanks for trying!

What did you try?

 

[Aladin Akyurek]

Re: Example...sorry

here is a quick example and some elaboration on my problem. I am using the equation:

=sumproduct((a1:a5>0)*(b1:b5="N")*(c1:c5))

c1, c2, and c5 have numbers. C3 and c4 have text so I get a #value error.

Is there anyway to make this work?

Just try what is already suggested...

=SUMPRODUCT(--(A1:A5 > 0),--(B1:B5 = "N"), C1:C5)