Formula Required

[Drewmyster]

Hi there,

I need a formula to count the number of occurrences that a piece of work was completed; therefore, a formula to look up a date range and match a person's name to that date.

For example;

I want to view from one page how many reports "Stacy" ran in January 2011 compared to her colleagues "Darren" "Jake" and "Michelle".

At present the data is arranged as follows;

04/01/2011 Stacy
08/01/2011 Jake
10/01/2011 Michelle
12/01/2011 Jake
15/01/2011 Jake
18/01/2011 Stacy
20/01/2011 Darren
22/01/2011 Stacy
24/01/2011 Stacy
26/01/2011 Michelle
28/01/2011 Jake
31/01/2011 Stacy

 

[kidwispa]

Assuming your data has headings and starts in A2 & B2:

=COUNTIF(B2:B13,B2)

would count the number of reports Stacy had ran...

 

[VoG]

Try like this

=SUMPRODUCT(--(MONTH(A1:A12)=1),--(B1:B12="Stacy"))

 

[T. Valko]

Hi there,

I need a formula to count the number of occurrences that a piece of work was completed; therefore, a formula to look up a date range and match a person's name to that date.

For example;

I want to view from one page how many reports "Stacy" ran in January 2011 compared to her colleagues "Darren" "Jake" and "Michelle".

At present the data is arranged as follows;

04/01/2011 Stacy
08/01/2011 Jake
10/01/2011 Michelle
12/01/2011 Jake
15/01/2011 Jake
18/01/2011 Stacy
20/01/2011 Darren
22/01/2011 Stacy
24/01/2011 Stacy
26/01/2011 Michelle
28/01/2011 Jake
31/01/2011 Stacy

What version of Excel are you using?

 

[Jonmo1]

Try

=SUMPRODUCT(--(TEXT(A2:A100,"mm/yyyy")="01/2011"),--(B2:B100="Stacy"))

Dates in column A, names in column B


Hope that helps.

 

[Drewmyster]

Further to the suggestions I should explain where there data is located;

The formula will in part need to lookup a date from another tab.

This is because the spreadsheet has many tabs for different reports that are run, so for examples sake, there could one titled "Account Balances"

Within Account Balances the date is column C and the person's name is column D.

I want the formula to be displayed within a Total Values tab which has dates running down column A and individual's names accross Row 1.

Therefore, if I use the formula =COUNTIF(Account Balances!C2:D12....) this will need to lookup and match the date range I am looking for.

Any advice please?