leave out blanks in SUMIFS excel formula-CodePudding

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?

CodePudding user response：

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))

CodePudding user response：

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

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