Counting occurences of value based on multiple criteria

[Keano]

Hi,

I am trying to count the number of times a specific date value occurs
for a specific team.

Basically, column A "Team" contains multiple entries of a team name
e.g. TeamA, TeamB, TeamC etc..

Column B "StartDate" contains various work start dates for each team
for different jobs e.g. 20031004, 20031001, 20030930 etc.

The dates are held a text.

What I am trying to calculate is the number of times a date occurs for
each team e.g. the number of jobs TeamA has starting on 20031004.

I have tried using the following tip:
{=SUM((Team=TeamA")*(StartDate="20031004"))}
but it doesn't work.
I have made the column headers labels and I have ticked the "Accept
labels in formulas" box. When I use the labelname in a single function
(e.g. COUNTIF) it works fine its just when I try to combine two that
the problems start.

Any help would be greatly appreciated.

Thanks

 

[Max]

Assuming the data is in A2:B6 (with row 1 = col headers)
col A = team name, col B = startdates

Put in F1: =SUMPRODUCT(($A$2:$A$6=D1)*($B$2:$B$6=E1))
where in D1 & E1 will be the team name & the startdate desired

F1 can then be copied down col F to return corresponding values
if cols D & E contain other team names & startdates

HTH

Max

 

[Felipe I]

You can use:

=SUMPRODUCT((Team="TeamA")*(StartDate=20031004))