Sumproduct returning FALSE for TRUE values?

[uceaamh]

Hello,

I'm struggling to get a SUMPRODUCT formula to work.

=IF(SUMPRODUCT((Table4[Premises - ID]=$A3)*1,(Table4[Notice Type]="PIN")*1)=1,"Y","N")

Here is the issue:

In the worksheet with the formula A3 = 59438. So, I'm looking for a 59438 in a separate worksheet, holding Table 4, under the "Premises - ID" Header.

There is a 59438 in Table 4, under "Premises - ID". There are 5 of them actually. When I highlight

Table4[Premises - ID]

in the formula and hit F9, I get the following string:

{"57052";"58828";"59438";"59234";"152614";"26724";"55659";"55354";"28320";"152622";"54905";"55081";"26567";"28043";"55211";"59168";"58882";"20498";...

(excerpted to save room). As you can see, 59438 is the third number to appear in the string.

BUT when I highlight

Table4[Premises - ID]=$A3

in the string and hit F9, I get a long row of:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;...

I'm not sure what is going on here? Shouldn't the matching 59438 return as TRUE?

This problem doesn't seem to be occurring in the second array, where

Table4[Notice Type]="PIN"

returns an appropriate blend of TRUE;FALSE

Thanks in advance!

 

[Aladin Akyurek]

Table4[Premises - ID] seem to house text numbers, not true numbers.

By the way, what are you trying to achieve?

 

[uceaamh]

You're right. Thanks for that.

That goal is to tell the user whether a premises ID # has an attached notice type, in this case "PIN". This formula is repeated for the other notice types that can be attached in other cells.

 

[shaowu459]

To avoid such problem, you can modify your formula as follows:

=IF(SUMPRODUCT((Table4[Premises - ID]-$A3=0)*1,(Table4[Notice Type]="PIN")*1)=1,"Y","N")

 

[markmzz]

Hi!

Try this too:

=IF(COUNTIFS(Table4[Notice Type],"PIN",Table4[Premises - ID],$A3),"Y","N")

Markmzz

 

[Aladin Akyurek]

You're right. Thanks for that.

That goal is to tell the user whether a premises ID # has an attached notice type, in this case "PIN". This formula is repeated for the other notice types that can be attached in other cells.

If you don't want to coerce the text number IDs into true number IDs, use $A3&"" in the formulas in what follows.

Assuming that an ID has always 1 notice type, not many:

=IFERROR(IF(VLOOKUP(A3,CHOOSE({1,2},Table4[Premises - ID],Table4[Notice Type]),2,0)="PIN","Y","N"),"not available")

A bit succinctly expressed...

=IFERROR((VLOOKUP($A3,CHOOSE({1,2},Table4[Premises - ID],Table4[Notice Type]),2,0)="PIN")+0,"not available")

where a 1 means a hit (yes), a 0 miss (no).

If an ID can be associated with multiple notice types:

In C3 control+shift+enter, not just enter:

=ISNUMBER(MATCH($A3,IF(Table4[Notice Type]="PIN",Table4[Premises - ID]),0))+0

 

[uceaamh]

Hi!

Try this too:

=IF(COUNTIFS(Table4[Notice Type],"PIN",Table4[Premises - ID],$A3),"Y","N")

Markmzz

That got it.

Thanks, @Aladin Akyurek and @shaowu459 for the suggestions as well, but I couldn't get the formulas to return properly. I kept just getting 0's - even after converting everything to numbers manually.

 

[Aladin Akyurek]

@ uceaamh

Aladin Akyurek... I kept just getting 0's - even after converting everything to numbers manually.

Now you might get into trouble with other types of processing...

As I said, you might use $A3&"" if you didn't succeed with conversion.

 

[markmzz]

Hi!

Try this too:

=IF(COUNTIFS(Table4[Notice Type],"PIN",Table4[Premises - ID],$A3),"Y","N")

Markmzz

That got it.

I'm glad to help and thanks for the return.

Markmzz