CountIfs Help

MHILLS

New Member
Joined
Oct 16, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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!


1729093871119.png
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
What does that formula return? & what should it return?
 
Upvote 0
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.

DateRoom
4/1/2024MOR 041
4/1/2024MOR 053
4/1/2024MOR 052
4/1/2024MOR 051
4/1/2024MOR 063
4/1/2024MOR 062
4/1/2024MOR 061
4/1/2024MOR 075
4/1/2024MOR 074
4/1/2024MOR 073
4/1/2024MOR 072
4/1/2024MOR 071
4/1/2024MOR 082
4/1/2024MOR 081
4/1/2024MOR 09 DaVinci2
4/1/2024MOR 09 DaVinci1
4/1/2024MOR 10 DaVinci4
4/1/2024MOR 10 DaVinci3
4/1/2024MOR 10 DaVinci2
4/1/2024MOR 10 DaVinci1
4/1/2024MOR 11 DaVinci3
4/1/2024MOR 11 DaVinci2
4/1/2024MOR 11 DaVinci1
4/1/2024SDOR 012
4/1/2024SDOR 011
4/1/2024SDOR 023
4/1/2024SDOR 022
4/1/2024SDOR 021
4/2/2024MOR 041
4/2/2024MOR 054
4/2/2024MOR 053
 
Upvote 0
You need to lock down the range references on your columns when you copy the formula down, i.e.
Rich (BB code):
=COUNTIFS(A$2:A$4402,A2,B$2:B$4402,B2)
 
Upvote 0
You need to lock down the range references on your columns when you copy the formula down, i.e.
Rich (BB code):
=COUNTIFS(A$2:A$4402,A2,B$2:B$4402,B2)
That did it, thank you so much!!
 
Upvote 0
You are welcome.
Glad we were able to help!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top