# sumif?countif?array?

#### Green Spandex

##### New Member
Hit the wall on what seems like a simple formula - something my brain copes with at work Mon-Fri but struggles with on the weekend!!

List of names in col A
List of years in col C

smith, 1997
jones, 1997
smith, 1997
wood, 1997
smith, 1997
smith, 1998
wood, 1998 etc.

A summary table has ALL NAMES in col U, with years (1997...2006) in row 1.

I need a formula that lists how many Smiths there was in 1997, Smiths in 1998, Jones in 1997 etc.

count all the times the smith in the summary table matches the smiths in col A AND the year in row 1 matches the year in col C....

Cheers

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Richard Schollar

##### MrExcel MVP
Assuming your year is an integer value (ie 1997 and not a date, 31/12/1997) then:

Code:
``=SUMPRODUCT(--(\$A\$2:\$A\$1000=\$U2),--(\$C\$2:\$C\$1000=V\$1))``

copied down/across and adjust ranges to suit.

Best regards

Richard

#### Green Spandex

##### New Member
Not sure that was what I was after, put it down to my crappy explanation/wording!!

List of players names in COLUMN A
List of round numbers in COLUMN B
List of seasons in COLUMN C

smith, 1, 1997
jones, 1, 1997
wood, 1, 1997
smith, 2, 1997
smith, 3, 1997
smith, 1, 1998
jones, 1, 1998
wood, 1, 1998
smith, 2, 1998

In my summary table I would like to see (3) returned as the number of games smith played in 1997, (2) for smith in 1998, etc.

#### Richard Schollar

##### MrExcel MVP
This is what my formula does:
Book1
ABCDEFGHIJKLMNOPQRSTUVW
1NameRoundNoSeasonUniqueName19971998
2Smith11997Smith32
3Jones11997Jones11
4Wood11997Wood11
5Smith21997
6Smith31997
7Smith11998
8Jones11998
9Wood11998
10Smith21998
Sheet1

Or is it something different you want?

Richard

#### Green Spandex

##### New Member
That's perfect, thank you.

I wasn't aware what the "---" represented in the sumproduct formula so left them out originally, probably why it didn't work.

Added an if statement to return "" for zero values - all looks in order.

Cheers mate.

#### Richard Schollar

##### MrExcel MVP
You're welcome

As regards the double minus sign, the ranges&criteria within the SUMPRODUCT return arrays (lists) of True/False dependent on whether the cell(s) matches the criteria. To utilise the True/False arrays, we have to turn them into arrays of 1s and 0s (1=True,0=False) and one way to do that is to use a --. Other ways are to add 0 or to multiply by 1, so the following are also equivalent:

Code:
``````=SUMPRODUCT((\$A\$2:\$A\$10=\$U2)*1,(\$C\$2:\$C\$10=V\$1)*1)

=SUMPRODUCT((\$A\$2:\$A\$10=\$U2)+0,(\$C\$2:\$C\$10=V\$1)+0)``````

Best regards

Richard

Replies
6
Views
166
Replies
3
Views
399
Replies
13
Views
1K
Replies
3
Views
642
Replies
3
Views
1K

1,171,579
Messages
5,876,284
Members
433,192
Latest member
butterexcel

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