CODEWITHSUNDEEP
excelsumifs
desto
desto

Reputation: 1137

Add numbers and then sumif in excel

What I want to do but can't see how can I do it:

=SUMIF(600 - C:C,A:A,"=--")

I want to sum the difference between 600 and the actual number, only if my the A column matches --

Currently I've created an extra column to hold the result of 600 - the C column, but I want to simplify my sheet.

How can I achieve this?

Upvotes: 0

Views: 49

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You will want to use SUMPRODUCT() and limit the range to the actual dataset:

=SUMPRODUCT((600-C2:C28)*(A2:A28="--"))

OR

If you want to avoid the array formula and keep the full column references then use this:

=(600*COUNTIF(A:A,"=--"))-SUMIF(A:A,"=--",C:C)

enter image description here

Upvotes: 5

Related Questions