Sumproduct and Blank Rows

[rex759]

Hello,

What I want to do is if the number in column C is > 0, multiple it by the number in column D.

Here is my formula:

=SUMPRODUCT(--(C2:C19>0),(C2:C19*E2:E19))

My formula works fine if the range does not include blank cells, otherwise, I get a #value error. I have tried a few variables without success.

Any help is appreciated

C2=IF(A2="","",SUM(A2-B2))

Original	Actual	Difference	Cost	Diff
23	23	0	26	0
24	24	0	27	0
25	26	-1	33	-33
22	27	-5	44	-220
1	0	1	55	55
77	77	0	66	0
46	46	0	127	0
53	52	1	231	231
32	32	0	59	0
13	12	1	12	12

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(1/C2:C19),C2:C19*E2:E19))

 

[rex759]

hmmm, that gave me a result of 1431, it should be 298

 

[Ron Coderre]

Try this ARRAY FORMULA (completed using CTRL+SHIFT+ENTER, instead of just ENTER):
=SUM(IFERROR((C2:C19>0)*(C2:C19)*(D2:D19),0))

Is that something you can work with?

 

[rex759]

I was hoping the formula would be easy to apply this =SUMPRODUCT(--(C2:C12<0),(C2:C12*D2:D12)) if the number in column C was less than zero

 

[Aladin Akyurek]

hmmm, that gave me a result of 1431, it should be 298

did you apply control+shift+enter? do you have negative numbers?

 

[rex759]

Ok, that worked for both of my formulas.

 

[Aladin Akyurek]

Ok, that worked for both of my formulas.

that’s great to hear.

 

[rex759]

I spoke too soon.
When I use this formula, ={SUM(IFERROR((C2:C20>0)*(C2:C20)*(D2:D20),0))} I should get 222.27. Instead, I get 170.18. Is this because of the "-" numbers?

Original	Actual	Difference	Cost	Diff
23	23	-1	194.22	-194.22
24	24	0	194.22	0
25	26	0	55.09	0
22	27	0	194.22	0
1	0	2	20	40
77	77	0	55.09	0
46	46	0	55.09	0
53	52	1	20	20
		-1	55.09	-55.09
32	32	-1	55.09	-55.09
13	12	-1	55.09	-55.09
22	24	2	55.09	165.27

 

[jasonb75]

How are you getting to 222.27?

=SUMPRODUCT(--(C2:C19>0),C2:C19,E2:E19)
=SUMPRODUCT(--(C2:C20>0),C2:C20,D2:D20)

should work for what you need