Using a named range in a Sumproduct comparison

[Hari]

Hi,

Lets suppose I have in a worksheet of 500 rows of data with 4 columns.

I want to apply sumproduct for determining count with certain conditions.

Now one of the columns (column A) have data such that it is always only one
of the following 6 possible values. "UK", "US", "IN", "AU", "FI" and "PA

Now one of the conditions in the sumproduct is that column A has to be "UK",
"AU" or "FIN".

For one other column the (Column B) there are 4 possible conditions like 56,
78, 89 or 44.

Now the next condition for sumproduct is that column B is that it has to be
either 78 or 44.

Rest of the two columns are single condition evaluation like column C values
should be equal to or greater than 50 and column D values should be equal
to "Fine"

Since my condition for counting was based on OR condition for column A and
Column B so I used named range for my wanted values.

Like I defined on Name as Country for "UK", "AU" or "FIN" and another Name
as Quantity for 78 or 44.

My sumproduct formula was -->

=SUMPRODUCT((A1:A500=Country)*(B1:B500=Quantity)*(C1:C500>=50)*(D1500="Fin
e"))

Im getting an answer as #N/A

When I used evaluate formula option toolbar (I have excel 2002) it seemed as
if the named ranges are being expected to be an array.

Where am i going wrong?

Regards,
Hari
India

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((A1:A500={"UK","AU","FI"})*(B1:B500={78,44})*(C1:C500>=50)*
(D1500="Fine"))

 

[Hari]

Hi Frank,

Thnx a lot for ur kind help

Regards,
Hari
India

 

[Hari]

Hi Frank,

Why doesnt named range work in this.

(Sometimes Excel is capable of things unimaginable things like array
formulas but it seems to sort of lose its power here.)

Regards,
Hari
India

 

[Frank Kabel]

Hi
you could do this also with cell ranges / defined names. One example
(sum column B if column A is equal either to one cell within the range
X1:X5):
=SUMPRODUCT(--(ISNUMBER(MATCH($A$1:$A$100,X1:X5,0))),B1:B100)

of course you can replace X1:X5 with a defined name:
=SUMPRODUCT(--(ISNUMBER(MATCH($A$1:$A$100,your_name,0))),B1:B100)


Have a look at the following site for more about this:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Dave Peterson]

That didn't work for me:

But this variation did:

=SUMPRODUCT(--((A1:A500="uk")+(A1:A500="au")+(A1:A500="fin")),
--((B1:B500=78)+(B1:B500=44)),
--(C1:C500>=50),
--(D1500="Fine"))

(all one cell)

If you add a few more strings to look for in a1:a500, it's sometimes easier to
see if =search() would return a number:

=SUMPRODUCT(--ISNUMBER(SEARCH("."&A1:A500&".",".uk.au.fin.")),
--((B1:B500=78)+(B1:B500=44)),
--(C1:C500>=50),
--(D1500="Fine"))

(still all one cell)


And with a named range:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A500,Country,0)),
--ISNUMBER(MATCH(B1:B500,Quantity,0)),
--(C1:C500>=50),
--(D1500="Fine"))

 

[Frank Kabel]

Hi
dave
yes, you're right. Problem is that the second element (column B check)
consist of only 2 and not 3 values. So thanks for spotting this.

 

[Hari]

Hi Frank,

What does it mean by second condition having only 2 values. Are u saying
that only 78 and 44 is there so it fails, while in 1st condition we have 3
values uk, fin and au.

Does the formula fail based on if less than 3 values?

Regards,
Hari
India

Hi
dave
yes, you're right. Problem is that the second element (column B check)
consist of only 2 and not 3 values. So thanks for spotting this.