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 calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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
504
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,192
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,192
@ 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,109,073
Messages
5,526,651
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top