SUMPRODUCT of result of formula

[jassybun]

=SUMPRODUCT((A$8:A$1000=6002)*(AD$8:AD$1000="Refer to ODSE"))

I am trying to get a count on cells in my AD column for a specific area (6002 in this case) that has the words "Refer to ODSE" in it, but unforunately, the formula gives me a "0" even if there is a row that in the AD column has "Refer to ODSE" and has an A column value of "6002". I imagine it is because "Refer to ODSE" is a result of a formula, and not actual stand alone text?

This is the first cell and second cell in the AD column to give you an idea of what I am looking at:
AD8:
=IF(COUNTIF($W$6:$W$1000,W8)=1,"",IF(COUNTIF($W$6:W8,W8)>1,"Duplicate",""))& IF(W8>0,IF(ISNUMBER(MATCH(W8,ODSE!$D:$D,0))," Refer to ODSE"," "),"")
AD9:
=IF(COUNTIF($W$6:$W$1000,W9)=1,"",IF(COUNTIF($W$6:W9,W9)>1,"Duplicate",""))& IF(W9>0,IF(ISNUMBER(MATCH(W9,ODSE!$D:$D,0))," Refer to ODSE"," "),"")

and so on...

I am using CTRL ALT ENTER.
Any idea what I am doing wrong?

 

[Xiq]

Hi jassybun,

My first guess is the extra space at the start of you text-string in " Refer to ODSE" is causing "problems". Either remove this in your AD column formula's or add it in your sumproduct formula.

 

[Xiq]

To followup,

A few ways to counter such problems is the use of these functions:

• TRIM()
• FIND() <- in combination of an IFERROR for negative results
• SEARCH() <- in combination of an IFERROR for negative results
• LEFT()
• RIGHT()
• MID()

 

[Faseeh]

jassyban,

Can you upload sample data? Thanks...

 

[Somendra Misra]

I think the extra space in the text " Refer to ODSE" is causing so.

Regards!

 

[jassybun]

OMG. I feel so stupid. I thought it was so much more complicated - I don't know why I thought the space didn't matter but of course it does! As they say "The Devil is in the details" ! That solved it, thanks everyone!