sumproduct with if statement?

**vinnygamz** · 10-19-2009, 10:48 AM

Volume Unit Cost Before
44 14.11 n/a
9 10.46 8.58
37 9.61 7.88
20 8.26 6.77
78 7.36 6.04
0 3.65 n/a

Can someone show me how to multiple column a*b if column c is "n/a". And column a*c if it has a number, with suming them at the end?

Thanks

**NBVC** · 10-19-2009, 10:53 AM

Try:

=SUMPRODUCT(--ISNUMBER(C2:C7),A2:A7,C2:C7)+SUMPRODUCT(--(C2:C7="n/a"),A2:A7,B2:B7)

**DonkeyOte** · 10-19-2009, 11:00 AM

Or, if preferred

=SUM(A2:A7*IF(ISNUMBER(C2:C7),C2:C7,B2:B7))
confirmed with CTRL + SHIFT + ENTER

**vinnygamz** · 10-19-2009, 11:01 AM

Not working for me. What does the "--" do?

**NBVC** · 10-19-2009, 11:04 AM

They coerce arrays of TRUE/FALSE to 1/0, respectively so Sumproduct can do the math...

How is it not working?

Are you sure you have the ranges correct?

Are the numbers in column C actually formatted as numbers?

Try selecting column C and go to Data|Text to Columns and click Finish.

**NBVC** · 10-19-2009, 11:07 AM

Here it is working with your data.

**vinnygamz** · 10-19-2009, 11:08 AM

I am confirming with "shift+ctrl+enter" and it still pulls an error. I have really never play with arrays before so I am kinda thinking I am messing it up.