Array formula issue:

I'm having issues with an array formula:

=IFERROR(SUM(IF(('data entry'!$G$37:$G$92='NEW avail-capa overview'!$Z4)*('data entry'!$H$37:$H$92='NEW avail-capa overview'!AA$3),'data entry'!$I$37:$I$92,"")),"")

Assume:
Z4 = Product 1
AA3 = CC1

If these values are both found, it returns the correct value/total I need. my problem arises when the values searched for aren't in the arrays. according to the coding, it should return "". currently it is returning a value of 0.

if i change the formula to:

=IFERROR(SUM(IF(('data entry'!$G$37:$G$92='NEW avail-capa overview'!$Z4)*('data entry'!$H$37:$H$92='NEW avail-capa overview'!AA$3),'data entry'!$I$37:$I$92,"none")),"")

or

=IFERROR(SUM(IF(('data entry'!$G$37:$G$92='NEW avail-capa overview'!$Z4)*('data entry'!$H$37:$H$92='NEW avail-capa overview'!AA$3),'data entry'!$I$37:$I$92,"1")),"")

it still returns a value of 0 not the 'none' or '1' values I'd expect.

if I change the code to:

=IFERROR(SUM(IF(('data entry'!$G$37:$G$92='NEW avail-capa overview'!$Z4)*('data entry'!$H$37:$H$92='NEW avail-capa overview'!AA$3),'data entry'!$I$37:$I$92,1)),"")

I get a completely random seeming value. i.e. not 1 and not 0 as expected.

any suggestions would be appreciated.

r/

SUPPO

ah. that makes sense. I appreciate the information. Looking at it now, I can see it's returning a blank string to the SUM function, which as you indicated is treating it as a number not a null value and will display that in the output. luckily for me, this formula was an intermediary step and I found a formula that converts any 0 values to "" when the final display is shown.

thanks again for the clarification.

r/

SUPPO