 # Matching blank cells in SUMIFs with multiple criteria

• I have a table that needs to automatically make calculations based on matching values in different columns and have attached the table. In the last column labeled Spec Mean, I want to subtract the mean value from the rows labeled NSB based on matched values of other columns (e.g. v.Con column = "NSB", value in 35S column matches the value in the current row). So if in the D1 column the value is -4.5, then it should subtract the value in the mean column from the row with NSB in the v.Con column and the value of -4.5 in the D1 column. This works unless the D1 column is blank. It doesn't subtract the value from the row with D1 blank even though they are both blank. How can I get it to match a blank value for these?

This is the value in the column I am using to calculate

=[@Mean]-(SUM(SUMIFS([Mean],[35S],[@35S],[v.Con],"NSB",[D1],[@D1])))

Thanks!

## Files

• SUMIFs.xlsx

• Try:

=[@Mean]-(SUM(SUMIFS([Mean],[35S],[@35S],[v.Con],"NSB",[D1],IF([@D1]="","",[@D1]))))

Where there is a will there are many ways. Finding one that works for you is the challenge!

MS Excel MVP 2010-2016

• Try:

=[@Mean]-(SUM(SUMIFS([Mean],[35S],[@35S],[v.Con],"NSB",[D1],IF([@D1]="","",[@D1]))))

Awesome that works, thanks!