SUMPRODUCT with condition

4

2

I have 2 columns B="Points" and C="Grade" And i'm trying to find the formula to sum the products of all points*grade for grades >= 60.

I tried this formula with many others, with no success:

SUMPRODUCT((B2:B100)*(C2:C100)*(C2:C100=">60"))

Output is: 0

Anyone has suggestions ?

Orel Eraki

Posted 2013-05-27T12:15:11.187

Reputation: 163

My suggestion is to create a new column and put this formula =IF(C2>=60;C2*B2;0) in it, make this for all rows and in the end sum it all. – Math – 2013-05-27T12:21:47.190

@Math i did it for the first place, because i know there is a better with no column usage. – None – 2013-05-27T12:23:38.793

Answers

10

Try this

=SUMPRODUCT(B2:B100,C2:C100,--(C2:C100>=60))

chris neilsen

Posted 2013-05-27T12:15:11.187

Reputation: 4 005

@Orel: I think you should mark his answer for your question. – Phuc Nguyen – 2014-12-31T04:42:43.817

@PhucNguyen, Yup. Marked + Up voted. – Orel Eraki – 2015-03-26T17:41:09.687

Asked: 2013-05-27T12:15:11.187

Viewed: 24 228 times

Active: 2013-05-27T18:59:59.020