Sumproduct or Something off the wall

[swaink]

Hi All I wonder if anyone is able to assist please.

I have a spreadsheet with many many rows and I need to count how many times a name appears is column B and the Item is in column G, where the name is that shown in AK27 and the Item in column G is in the list at "AN27:AN33."

I would normally use sumproduct and this would mean me transposing the item list and then performing the sumproduct in each column. i.e. in cell AL27 formula =sumproduct(--(B:B = AK27),--(G:G = AN27))

My question is doe's anyone know if it possible to use sumproduct where the name = "Barrry" and Barry has any of the items in the list, the aim would be to have one total figure for all of the items rather than 7 seperate columns of figures.

I know its off the wall

Kevin

QCC Data Changer 4.2.xls
	AK	AL	AM	AN
26	Name			Item
27	Barry			ItemA
28	Claire			ItemB
29	Philip			ItemH
30	Robert			ItemK
31	Nigel			ItemL
32	Chris			ItemM
33	Eric			ItemW
34	Rodney
35	Sharron
36	Peter
37	Kevin
38	Shaun
39	Jeannette
40	Rachele
41	Susan
42	Heather
43	David
44	Pamela
45	Gordon
46	Craig
47	Gregory
SheetA

 

[Colin Legg]

Hi,

Try something like this:

=SUMPRODUCT(
    --ISNUMBER(MATCH(G27:G60,AN27:AN33,0)),
    --(B27:B60=AK27))

Adjust the G27:G60 and B27:B60 ranges to suit, but note that they cannot reference the entire column unless you are using Excel 2007.

Hope that helps

 

[swaink]

Hi Colin

Many thanks for the quick reply.

This works fine and is far more simple than the way I would have tackled it , I am glad I asked the question

Appreciate your help

Kevin