(IF(ISNA(MATCH along with named range array and sumproduct 1

[TomYC]

I have a formula such as the following, modified for simplicity here:
=SUMPRODUCT((JAN!$D$2:$D1000<>"")*(IF(ISNA(MATCH(JAN!$D$2:$D$1000,UNITS)),1,0)))
I'm expecting this formula to parse a column, and if the cell is not empty, to see if the contents of the cell match one of the elements of an array, a named range of cells somewhere else in the file called UNITS.
Currently the worksheet JAN contains 50 items, and most, but not all, of the items in col. D are contained in the comparison array UNITS. So I am expecting to return a number somewhat less than 50 but I am getting 50. (By the way, if I reverse the true/false options to be 0,1 instead of what displays above, I return zero.).
Surely this should work, no?

 

[SkipVought]

hi,

I do not see any parsing in your formula???

If you have 50 rows in your table, why is your row range 2:1000?

Where is UNITS in your table of rows 2:1000 and how does it relate to the data in D21000"|?

Please post a representative sample of data that illustrates the counting that you are trying to perform.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

[tt]
=SUMPRODUCT(--(JAN!$D$2:$D1000<>"")*(IF(NOT(ISNA(MATCH(JAN!$D$2:$D$1000,UNITS[highlight],0[/highlight]))),1,0)))
[/tt]
and enter your formula as an [highlight]ARRAY[/highlight]!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

laos check out the [highlight]NOT()[/highlight] included

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TomYC]

Thank you Skip, and have a Friday afternoon star!
I guess I thought that SUMPRODUCT made the array entering unnecessary, that it reproduced an array in an alternate manner--
T.Y.

 

[SkipVought]

the MATCH() using lookup range in range rather than value in range, is what requires the ARRAY entering necessary.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[firefytr]

You can just simply use...

=COUNT(MATCH(JAN!$D$2:$D1000,UNITS,0))

Also, array-entered, to simply get a count of values which do not match. If you do have blanks in UNITS range this formula will still work, as it won't count blanks.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci