CODEWITHSUNDEEP
excelexcel-formula
user3471438
user3471438

Reputation: 375

leave out blanks in SUMIFS excel formula

enter image description here

In the above example, using sumifs, the sum of D comes as 0. But since there is no value for D, the result should be blank.

Using this formula does not help.

=SUMIFS($B$2:$B$12,$B$2:$B$12,"<>",$A$2:$A$12,D3)

Any suggestions anyone?

Upvotes: 2

Views: 707

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

enter image description here

Try:

=IF(COUNTIF($A$2:$A$12;D2)=COUNTIFS($A$2:$A$12;D2;$B$2:$B$12;"");"";SUMIFS($B$2:$B$12;$A$2:$A$12;D2))

With commas:

=IF(COUNTIF($A$2:$A$12,D2)=COUNTIFS($A$2:$A$12,D2,$B$2:$B$12,""),"",SUMIFS($B$2:$B$12,$A$2:$A$12,D2))

Upvotes: 1

Ike
Ike

Reputation: 13024

If you have EXCEL 365 you can use the FILTER function:

=IFERROR(SUM(FILTER(tblData[count],(tblData[stat]=D3)*(tblData[count]<>""),"")),"no values")

enter image description here

Upvotes: 1

Related Questions