SumProduct/IF Formula That Excludes Blanks

[acool]

Hi Everyone! I am currently trying to create a Sumproduct Formula that Excludes Blanks from its total calculation. More specifically, I am trying to exclude any values where there are blanks in Column B. As it currently stands, I am calculating the Total Sumproduct of Column C & A using the Formula Sumproduct(C2:C9, A2:A9), however I would like my sumproduct to exclude any values from its calculation where there is a blank in column B. Any help or insight would be greatly appreciated! Thank You!

 

[Dave Patton]

N.B. You can post an extract of your information with the forum's tool named XL2BB.

T202306a.xlsm
	A	B	C	D
1	Units	MSRP	COGS
2	20	10	5
3	20	5	2.5
4	20	12	6
5	20		6
6	20		6
7	20	13	7
8	20		7
9	20	15	14
10
11			690	690
12
2b

Cell Formulas
Range	Formula
C11	C11	=SUMPRODUCT(C2:C9,--(B2:B9>0), A2:A9)
D11	D11	=SUM(IF(B2:B9>0,A2:A9*C2:C9))

 

[acool]

N.B. You can post an extract of your information with the forum's tool named XL2BB.

T202306a.xlsm
	A	B	C	D
1	Units	MSRP	COGS
2	20	10	5
3	20	5	2.5
4	20	12	6
5	20		6
6	20		6
7	20	13	7
8	20		7
9	20	15	14
10
11			690	690
12
2b

Cell Formulas
Range	Formula
C11	C11	=SUMPRODUCT(C2:C9,--(B2:B9>0), A2:A9)
D11	D11	=SUM(IF(B2:B9>0,A2:A9*C2:C9))

This did the trick thank you!

 

[Dave Patton]

Thanks for the feedback.