CountIfs Help

[MHILLS]

I have a large data spreadsheet (4,402 rows) and need to count the number of times a room (Col B) was used on a specific date (Col A). I am having a lot of trouble getting a formula that counts only the needed data points.

For instance, on 4/1/2024, MOR 05 should = 3, and on the same date, MOR 07 should = 5.

The date range is from 4/1/2024 to 9/30/2024, and there are 13 total rooms. I have scoured the internet for tutorials, but I haven't landed on the correct formula. Any help would be very appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
You can use the Countifs function for that. COUNTIFS Function

 

[MHILLS]

Hi & welcome to MrExcel.
You can use the Countifs function for that. COUNTIFS Function

That's what I have been trying to use, but I'm not doing something right, because it is not giving me the right room count for the day.

 

[Joe4]

Can you post the actual formula you tried, and we can see if there is an issue with it?
If your formula looks good to us, that would tell us that it is probably a data issue and not a formula issue.

 

[MHILLS]

=COUNTIFS(A2:A4402,A2,B2:B4402,B2)

 

[Fluff]

What does that formula return? & what should it return?

 

[MHILLS]

This is what it is returning. I need it to count how many times the same room is listed on the same date.

4/1/2024 should total like this: MOR 04 = 1, MOR 05 = 3, MOR 06 = 3, MOR 07 = 5, MOR 08 = 8, MOR 09 = 2.....

4/2/204 may have different totals for each room based on its use that day, and so on and so forth through September 30th.

Date	Room
4/1/2024	MOR 04	1
4/1/2024	MOR 05	3
4/1/2024	MOR 05	2
4/1/2024	MOR 05	1
4/1/2024	MOR 06	3
4/1/2024	MOR 06	2
4/1/2024	MOR 06	1
4/1/2024	MOR 07	5
4/1/2024	MOR 07	4
4/1/2024	MOR 07	3
4/1/2024	MOR 07	2
4/1/2024	MOR 07	1
4/1/2024	MOR 08	2
4/1/2024	MOR 08	1
4/1/2024	MOR 09 DaVinci	2
4/1/2024	MOR 09 DaVinci	1
4/1/2024	MOR 10 DaVinci	4
4/1/2024	MOR 10 DaVinci	3
4/1/2024	MOR 10 DaVinci	2
4/1/2024	MOR 10 DaVinci	1
4/1/2024	MOR 11 DaVinci	3
4/1/2024	MOR 11 DaVinci	2
4/1/2024	MOR 11 DaVinci	1
4/1/2024	SDOR 01	2
4/1/2024	SDOR 01	1
4/1/2024	SDOR 02	3
4/1/2024	SDOR 02	2
4/1/2024	SDOR 02	1
4/2/2024	MOR 04	1
4/2/2024	MOR 05	4
4/2/2024	MOR 05	3

 

[Joe4]

You need to lock down the range references on your columns when you copy the formula down, i.e.

=COUNTIFS(A$2:A$4402,A2,B$2:B$4402,B2)

 

[MHILLS]

You need to lock down the range references on your columns when you copy the formula down, i.e.

=COUNTIFS(A$2:A$4402,A2,B$2:B$4402,B2)

That did it, thank you so much!!

 

[Joe4]

You are welcome.
Glad we were able to help!