swaink
Active Member
- Joined
- Feb 15, 2002
- Messages
- 432
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
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 |