SUMPRODUCT CONTINUED

[kpro]

I am self taught and don't always know the right way to ask the questions to google. 2 days ago I posted my sumproduct formula and with help it worked perfectly. Now I am trying to add an IF for a zero return, (which is ok, I need the zeros to populate), but if it is not a zero, I need it to be whatever the SUMPRODUCT formula is populating. I used in cell K4 =iferror(if(sumproduct(data))="0",K4,"Not Available"),"") and it only will populate with "Not Available" even if the sumproduct result was 100. I also do not have to use the IF formula, I just need the formulated 0 to result in "Not Available" and any other number formulated to stay that number. Any ideas?

Thank you in advance for any help.

 

[Alex Blakenburg]

Remove the quote marks around your 0

 

[kpro]

Remove the quote marks around your 0

It is still populating Not Available even though the formulated result is 115.9

if i take it away, it works perfectly

 

[kpro]

It is still populating Not Available even though the formulated result is 115.9

View attachment 103444
View attachment 103445

if i take it away, it works perfectly
View attachment 103446

sorry, here is the formula
=IFERROR(SUMPRODUCT((Sheet2!$A$3:$A$857=Sheet1!A4)*(Sheet2!$B$3:$B$857=Sheet1!B4)*(Sheet2!$C$3:$C$857=Sheet1!C4)*(Sheet2!$D$3:$D$857=D4)*(Sheet2!$E$3:$E$857=E4)*(Sheet2!$F$3:$F$857=Sheet1!F4)*(Sheet2!$G$3:$G857=G4)*(Sheet2!$H3:$H857=Sheet1!H4)*(Sheet2!$I$3:$I$857)),"")

 

[Alex Blakenburg]

I think your logic is back to front, change it to <>0.
I assume you want K4 when its not 0 and Not Available when it is 0.
Also get rid of the Sheet1 references it just clutters up the formula.

Adjusted formula

=IFERROR(IF(SUMPRODUCT((Sheet2!$A$3:$A$857=A4)*(Sheet2!$B$3:$B$857=B4)*(Sheet2!$C$3:$C$857=C4)*(Sheet2!$D$3:$D$857=D4)*(Sheet2!$E$3:$E$857=E4)*(Sheet2!$F$3:$F$857=F4)*(Sheet2!$G$3:$G857=G4)*(Sheet2!$H3:$H857=H4)*(Sheet2!$I$3:$I$857))<>0,K4,"Not Available"),"")

Easier to read version:
(using alt+enter)

=IFERROR(IF(SUMPRODUCT((
Sheet2!$A$3:$A$857=A4)
*(Sheet2!$B$3:$B$857=B4)
*(Sheet2!$C$3:$C$857=C4)
*(Sheet2!$D$3:$D$857=D4)
*(Sheet2!$E$3:$E$857=E4)
*(Sheet2!$F$3:$F$857=F4)
*(Sheet2!$G$3:$G857=G4)
*(Sheet2!$H3:$H857=H4)
*(Sheet2!$I$3:$I$857))<>0,K4,"Not Available"),"")

 

[kpro]

I think your logic is back to front, change it to <>0.
I assume you want K4 when its not 0 and Not Available when it is 0.
Also get rid of the Sheet1 references it just clutters up the formula.

Adjusted formula

=IFERROR(IF(SUMPRODUCT((Sheet2!$A$3:$A$857=A4)*(Sheet2!$B$3:$B$857=B4)*(Sheet2!$C$3:$C$857=C4)*(Sheet2!$D$3:$D$857=D4)*(Sheet2!$E$3:$E$857=E4)*(Sheet2!$F$3:$F$857=F4)*(Sheet2!$G$3:$G857=G4)*(Sheet2!$H3:$H857=H4)*(Sheet2!$I$3:$I$857))<>0,K4,"Not Available"),"")

Easier to read version:
(using alt+enter)

=IFERROR(IF(SUMPRODUCT((
Sheet2!$A$3:$A$857=A4)
*(Sheet2!$B$3:$B$857=B4)
*(Sheet2!$C$3:$C$857=C4)
*(Sheet2!$D$3:$D$857=D4)
*(Sheet2!$E$3:$E$857=E4)
*(Sheet2!$F$3:$F$857=F4)
*(Sheet2!$G$3:$G857=G4)
*(Sheet2!$H3:$H857=H4)
*(Sheet2!$I$3:$I$857))<>0,K4,"Not Available"),"")

It is telling me it is a circular reference and changing it to 0

 

[Alex Blakenburg]

What sheet and cell is your formula in ?
Also is there a formula in K4 ? If so show me the formula.
If you change K4 in the formula with "Available" does the formula work in principle ?