SUMPRODUCT When Cell is blank then show blank not Zero 0

[farrukh]

Hi Experts!
By using SUMPRODUCT function, if the cell is blank then show blank in the formula results rather then zero 0 .
The Below Formula works ok but when there is a blank cell comes it shows 0 value rather then blank cell.

I am attaching the excel file.


=SUMPRODUCT(RawData!$DM$2:$DM$4000*((RawData!$DC$2:$DC$4000=B7)*(RawData!$DE$2:$DE$4000=$C$4)))

[ChemistB]

How about this

Please Login or Register  to view this content.

[johnmpl]

Hi, to both!

If you use SUMPRODUCT with ranges of same dimensions, I prefer to use the matrix form, getting better performance:

=IFERROR(1/(1/SUMPRODUCT(RawData!$DM$2:$DM$4000,--(RawData!$DC$2:$DC$4000=B6),--(RawData!$DE$2:$DE$4000=$C$4))),"")

Better than SUMPRODUCT in this case, SUMIFS:

=IFERROR(1/(1/SUMIFS(RawData!$DM$2:$DM$4000,RawData!$DC$2:$DC$4000,B6,RawData!$DE$2:$DE$4000,$C$4)),"")

Another option, instead of IFERROR(1/(1/...),""), could be use custom formatting, hidding zeros. Blessings!

[farrukh]

Hi Chemist / Johnmpl,
Thank you for your kind help. It works great if we have a blank cell but one thing if the cell value is Zero 0 then formula fetch the result value 0 .
I mean if Cell has no value ( Blank) then result blank or N/A , if the cell value is zero 0 then result required to be 0 not blank.

[johnmpl]

Hi, again!

You can try this formula instead:
=IFERROR(--(LOOKUP(2,1/(RawData!DC$2:DC$4000=B6)/(RawData!DE$2:DE$4000=C$4),RawData!DM$2:DM$4000)&""),"")

Blessings!

[farrukh]

Perfect works ! Thank you !