Sumproduct returning FALSE for TRUE values?

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
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!
 

Some videos you may like

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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Table4[Premises - ID] seem to house text numbers, not true numbers.

By the way, what are you trying to achieve?
 

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
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
Joined
Apr 26, 2018
Messages
511
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 7, 2011
Messages
3,699

ADVERTISEMENT

Hi!

Try this too:

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

Markmzz
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Feb 23, 2017
Messages
27

ADVERTISEMENT

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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
@ 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,886
Messages
5,544,868
Members
410,642
Latest member
Launayvolone
Top