sumif?countif?array?

Green Spandex

New Member
Joined
Aug 14, 2006
Messages
10
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Aug 14, 2006
Messages
10
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Aug 14, 2006
Messages
10
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
Joined
Apr 19, 2005
Messages
23,707
You're welcome :biggrin:

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
 

Forum statistics

Threads
1,137,366
Messages
5,681,072
Members
419,950
Latest member
BeckiJae

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top