How do I create a formula for summing the product of cells, based on their value?

Question

I have two columns of values. I want to add up the product of the two values, where the 2nd is grater than 1.

Now I'm using a separate 3rd column to aid in this and sumif the values based on the x2 column.

=SUMIF(B1:B9, ">0", C1:C9)

The result in this case should be 12+16+40 = 68

Is it possible to do the same without the 3rd column?

Pretty sure this has been answered numerous times before. – user692942 Jan 11 '22 at 16:54 — user692942, Jan 11 '22 at 16:54

score 2 · Accepted Answer · answered Jan 11 '22 at 16:43

2

Below should work

=SUMPRODUCT((A2:A6)*(B2:B6)*(B2:B6>1))

answered Jan 11 '22 at 16:43

Harun24hr

1

or even just SUM()? – DS_London Jan 11 '22 at 16:45
Fwiw, I think this answer is neater than the one I gave above ... – DS_London Jan 11 '22 at 16:52
This will work on all versions of excel while Filter() will work only on Excel-365. – Harun24hr Jan 11 '22 at 16:53
2

Yes, that's why I think it is a better answer! – DS_London Jan 11 '22 at 16:54
1

Thank you very much, I didn't think about the possibility to basically create and use an array with Boolean values – FeaxR Jan 11 '22 at 16:56
It's also been covered [numerous times before](https://www.google.com/search?q=site%3Astackoverflow.com+excel+sum+value+based+on+value). – user692942 Jan 11 '22 at 16:57
Somehow I missed them, probably because I searched for a too specific question, not a broad one – FeaxR Jan 11 '22 at 17:00

score 1 · Answer 2 · answered Jan 11 '22 at 16:41

1

Use the FILTER() function, using the 2nd column as the test.

=SUM(FILTER(A1:A5*B1:B5,B1:B5>1))

answered Jan 11 '22 at 16:41

DS_London

score 1 · Answer 3 · answered Jan 11 '22 at 16:52

1

I found another solution that I have somehow missed before

=SUMPRODUCT( --(B1:B9 > 1), A1:A9, B1:B9)

answered Jan 11 '22 at 16:52

FeaxR

3 Answers3