sumifs with Countif to exclude zero

[howard]

I have the following below that if the sumifs formula returns zero, then a zero must be returned, otherwise use the countifs formula


However, if the sumifs returns for eg 0.00000000183 or -0.0000000164, then the countifs value will result


I would like to amend my formula to exclude all zeroes which has a value after the decimal point as per example above


code]=IF(SUMIFS('Vat%'!B:B,'Vat%'!G:G,'Vat Payable per Vat %'!A5)=0,0,COUNTIFS('Vat%'!G:G,'Vat Payable per Vat %'!A5,'Vat%'!B:B,"<>0")) [/code]



your assistance is most appreciated

 

[wideboydixon]

Tricky without the data but this might work:

=IF(ABS(SUMIFS('Vat%'!B:B,'Vat%'!G:G,'Vat Payable per Vat %'!A5))<0.1,0,COUNTIFS('Vat%'!G:G,'Vat Payable per Vat %'!A5,'Vat%'!B:B,"<>0"))

WBD

 

[howard]

Thanks for the help. Formula works perfectly