CODEWITHSUNDEEP
excel
RickyD
RickyD

Reputation: 11

How do i get Sumifs to return a null value if the cell is null over multiple ranges

I've been struggling with this for a while now, i use a Sumifs in excel to evaluate over a range of data,

My problem is that it returns the blank cells as zeros instead of blank,

is there a way to make the cell return as blank when looking at multiple ranges,

enter image description here

The above link is what the data looks like and the below link is what the sumifs returns, i need it to return blanks where the data is blank instead of zero,

enter image description here

The equation i am using is =SUMIFS(Sheet1!C:C;Sheet1!$A:$A;Sheet2!$A2;Sheet1!$B:$B;Sheet2!$B2) where sheet 1 is the sheet with the data and sheet 2 is the table where the sumifs evaluates to,

Also if the value is blank in the data it must return blank, but if it is zero in the data it must return as a zero.

Please someone help me.

Upvotes: 1

Views: 974

Answers (1)

Imran Malek
Imran Malek

Reputation: 1719

You can use IF condition to check if the total is zero.

=IF(SUMIFS(Sheet1!C:C,Sheet1!$A:$A,Sheet2!$A2,Sheet1!$B:$B,Sheet2!$B2)=0,"",SUMIFS(Sheet1!C:C,Sheet1!$A:$A,Sheet2!$A2,Sheet1!$B:$B,Sheet2!$B2))

Apply cell formatting to hide zeros 0;-0;;@

Upvotes: 1

Related Questions