Counting entries with multiple criteria

[Trentsmith]

I am working with a sheet that tracks client meeting dates set by 6 different agents. One column has the dates, and another has the name of the agent that set the meeting. I am trying to write a formula to count the number of appointments set by each agent each month.

I have been using the following formula to count the total number of appointments set: Countif(B:B, “Jason”) but I want to break that down by month without needing to manually set ranges at the end of the month.

I tried multiple different formulas using Countifs, but every time I added formulas that count the number of August months, for example, the result would be zero if I also included the part that counts the agent’s name frequency.

In the actual sheet, agent names are in column B and dates are in column D.

 

[Marcelo Branco]

Welcome to Mr Excel

Try something like this (dates as dd/mm/yyyy)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Agent​

[/td][td][/td][td]

Date​

[/td][td][/td][td]

Month​

[/td][td]

Agent​

[/td][td]

Count​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Anthony​

[/td][td][/td][td]

10/09/2017​

[/td][td][/td][td]

01/09/2017​

[/td][td]

Anthony​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bob​

[/td][td][/td][td]

12/09/2017​

[/td][td][/td][td][/td][td]

Bob​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Charles​

[/td][td][/td][td]

12/09/2017​

[/td][td][/td][td][/td][td]

Charles​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Richard​

[/td][td][/td][td]

14/09/2017​

[/td][td][/td][td][/td][td]

Jason​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Jason​

[/td][td][/td][td]

15/09/2017​

[/td][td][/td][td][/td][td]

Mary​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Jason​

[/td][td][/td][td]

20/09/2017​

[/td][td][/td][td][/td][td]

Richard​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Anthony​

[/td][td][/td][td]

22/09/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Mary​

[/td][td][/td][td]

23/09/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Anthony​

[/td][td][/td][td]

02/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Bob​

[/td][td][/td][td]

03/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Jason​

[/td][td][/td][td]

04/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

Mary​

[/td][td][/td][td]

03/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

Richard​

[/td][td][/td][td]

04/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

Mary​

[/td][td][/td][td]

05/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

Charles​

[/td][td][/td][td]

05/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

Charles​

[/td][td][/td][td]

06/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Month of interest in F2 (a date = first day of the month)

List of agents in G2:G7

Formula in H2 copied down
=COUNTIFS(B$2:B$100,G2,D$2:D$100,">="&F$2,D$2:D$100,"<="&EOMONTH(F$2,0))

Hope this helps

M.

 

[Trentsmith]

Thank you! Is there a way to reference the start date within the formula rather than having a column of start dates?

Welcome to Mr Excel

Try something like this (dates as dd/mm/yyyy)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Agent​

[/td][td][/td][td]

Date​

[/td][td][/td][td]

Month​

[/td][td]

Agent​

[/td][td]

Count​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Anthony​

[/td][td][/td][td]

10/09/2017​

[/td][td][/td][td]

01/09/2017​

[/td][td]

Anthony​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bob​

[/td][td][/td][td]

12/09/2017​

[/td][td][/td][td][/td][td]

Bob​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Charles​

[/td][td][/td][td]

12/09/2017​

[/td][td][/td][td][/td][td]

Charles​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Richard​

[/td][td][/td][td]

14/09/2017​

[/td][td][/td][td][/td][td]

Jason​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Jason​

[/td][td][/td][td]

15/09/2017​

[/td][td][/td][td][/td][td]

Mary​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Jason​

[/td][td][/td][td]

20/09/2017​

[/td][td][/td][td][/td][td]

Richard​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Anthony​

[/td][td][/td][td]

22/09/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Mary​

[/td][td][/td][td]

23/09/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Anthony​

[/td][td][/td][td]

02/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Bob​

[/td][td][/td][td]

03/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Jason​

[/td][td][/td][td]

04/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

Mary​

[/td][td][/td][td]

03/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

Richard​

[/td][td][/td][td]

04/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

Mary​

[/td][td][/td][td]

05/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

Charles​

[/td][td][/td][td]

05/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

Charles​

[/td][td][/td][td]

06/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Month of interest in F2 (a date = first day of the month)

List of agents in G2:G7

Formula in H2 copied down
=COUNTIFS(B$2:B$100,G2,D$2:D$100,">="&F$2,D$2:D$100,"<="&EOMONTH(F$2,0))

Hope this helps

M.

 

[Marcelo Branco]

Thank you! Is there a way to reference the start date within the formula rather than having a column of start dates?

Not sure i understand what you are looking for. Could you elaborate?

M.

 

[Trentsmith]

Your formulas in column H reference cell F2 to use as the start date. I would like to be able to place that start date directly into the formula and eliminate that date.

However, I used your formula and repeated it down for 24 months, and I’m assuming the formula wouldn’t automatically change the month from row to row if I input “09/01/2017” directly into the formula.

Does that make it any clearer?

Not sure i understand what you are looking for. Could you elaborate?

M.

 

[Marcelo Branco]

Yes, you can do this, for example:
=COUNTIFS(B$2:B$100,G2,D$2:D$100,">="&DATE(2017,9,1),D$2:D$100,"<="&EOMONTH(DATE(2017,9,1),0))

But then, each month you will have to manually change the year and month in the formula.
Do you really want this? It seems to me much better simply to change the value of F2 (reference date).

EDIT
You said you copy down the formula for 24 months.
Where (columns) do you have the year and month?

M.

 

[Marcelo Branco]

However, I used your formula and repeated it down for 24 months, and I’m assuming the formula wouldn’t automatically change the month from row to row if I input “09/01/2017” directly into the formula.

See if this does what you need


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Agent​

[/td][td][/td][td]

Date​

[/td][td][/td][td]

Ref Date​

[/td][td]

Anthony​

[/td][td]

Bob​

[/td][td]

Charles​

[/td][td]

Jason​

[/td][td]

Mary​

[/td][td]

Richard​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Anthony​

[/td][td][/td][td]

10/09/2017​

[/td][td][/td][td]

01/09/2017​

[/td][td]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bob​

[/td][td][/td][td]

12/09/2017​

[/td][td][/td][td]

01/10/2017​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

1​

[/td][td]

2​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Charles​

[/td][td][/td][td]

12/09/2017​

[/td][td][/td][td]

01/11/2017​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Richard​

[/td][td][/td][td]

14/09/2017​

[/td][td][/td][td]

01/12/2017​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Jason​

[/td][td][/td][td]

15/09/2017​

[/td][td][/td][td]

01/01/2018​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Jason​

[/td][td][/td][td]

20/09/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Anthony​

[/td][td][/td][td]

22/09/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Mary​

[/td][td][/td][td]

23/09/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Anthony​

[/td][td][/td][td]

02/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Bob​

[/td][td][/td][td]

03/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Jason​

[/td][td][/td][td]

04/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

Mary​

[/td][td][/td][td]

03/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

Richard​

[/td][td][/td][td]

04/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

Mary​

[/td][td][/td][td]

05/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

Charles​

[/td][td][/td][td]

05/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

Charles​

[/td][td][/td][td]

06/10/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Put a date in F2 that corresponds to day 1 of the first month of interest.
In F3 copied down as long as you need
=EOMONTH(F2,0)+1

Formula in G2 copied across and down
=COUNTIFS($B$2:$B$1000,G$1,$D$2:$D$1000,">="&$F2,$D$2:$D$1000,"<="&EOMONTH($F2,0))

M.