CODEWITHSUNDEEP
excelexcel-formula
Shinaj
Shinaj

Reputation: 105

Calculate sum of a range as per cell condition and value

In the attached picture ever column from B to G has value mentioned in red. eg) col. B has value 15. I am looking the sum of values in all these columns minus the value where the cell value is "NO". The result should be displayed under col. H

eg) for row 3, the value "No" is in cell C3 and E3 and there resp. values are 10 and 5. So the total value in cell H3 should be sum of value in A to G (15 + 10+5+5+10+25) minus 10 and 5 = 55.

I think we need to user IF function clubbed with may be sumproduct. I tried but not getting anywhere. Please help.

enter image description here

Upvotes: 1

Views: 981

Answers (1)

BigBen
BigBen

Reputation: 50162

Use SUMIF or SUMIFS:

=SUMIFS(B$2:G$2,B3:G3,"<>NO")

enter image description here

or SUMPRODUCT:

=SUMPRODUCT($B$2:$G$2*(B3:G3<>"NO"))

Upvotes: 2

Related Questions