# Sumproduct returning FALSE for TRUE values?

#### uceaamh

##### New Member
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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

By the way, what are you trying to achieve?

#### uceaamh

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP

Hi!

Try this too:

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

Markmzz

##### MrExcel MVP
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

##### New Member

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.

Last edited:

##### MrExcel MVP
@ 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

##### MrExcel MVP
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