# Sumproduct Problem

#### RAF1112

##### Board Regular
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!

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
RAF1112 said:
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).

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?

Re: Example...sorry

RAF1112 said:
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.

Darn

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

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

Re: Darn

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

What did you try?

Re: Example...sorry

RAF1112 said:
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)

Replies
13
Views
448
Replies
19
Views
629
Replies
15
Views
248
Replies
3
Views
249
Replies
11
Views
589

1,196,487
Messages
6,015,487
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back