# Sumproduct or Something off the wall

#### swaink

##### Active Member
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
AKALAMAN
26NameItem
27BarryItemA
28ClaireItemB
29PhilipItemH
30RobertItemK
31NigelItemL
32ChrisItemM
33EricItemW
34Rodney
35Sharron
36Peter
37Kevin
38Shaun
39Jeannette
40Rachele
41Susan
42Heather
43David
44Pamela
45Gordon
46Craig
47Gregory
SheetA

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Colin Legg

##### MrExcel MVP
Hi,

Try something like this:
Code:
``````=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

##### Active Member
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

Kevin

Replies
1
Views
2K
Replies
5
Views
584
Replies
13
Views
2K
Replies
40
Views
2K
Replies
1
Views
1K

1,195,668
Messages
6,011,056
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back