Question description:
This user has given permission to use the problem statement for this
blog.
Hi there, I have data that has Week Number, Day and shift colour (Red or BLue) and need a formula that would count and bring back the details such as how many times Blue on week 16 on Mon and not anything else (hope this makes sence)
Solved by X. E. in 8 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
18/05/2018 - 12:34
Hello, I understand that you need help counting data that meet your criteria, right?
User
18/05/2018 - 12:34
correct
Excelchat Expert
18/05/2018 - 12:35
Alright, we can definitely help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
Excelchat Expert
18/05/2018 - 12:35
Do you have a file you can share so I can analyze it and offer the best possible solution?
User
18/05/2018 - 12:35
sorry no
Excelchat Expert
18/05/2018 - 12:35
A sample data that has accurate sheet names and column locations would suffice if you don't.
Excelchat Expert
18/05/2018 - 12:36
You can use the Document Preview to the right to create a sample data.
Excelchat Expert
18/05/2018 - 12:37
Alright, for this requirement, we can use the COUNTIFS() function. Know that the formula I'll be giving you will only work for the particular data set you've provided.
User
18/05/2018 - 12:38
So i want to be able to have a forumal that would count how many times Blue Week16 Tue
Excelchat Expert
18/05/2018 - 12:39
The formula in E2 will count the number of records with Blue, Week 16 and Mon.
Excelchat Expert
18/05/2018 - 12:39
=countifs(A:A,"Blue",B:B,"Week 16",C:C,"Mon")
Excelchat Expert
18/05/2018 - 12:39
Basically it says, count IF column A:A is Blue and B:B is Week 16 and C:C is Mon.
User
18/05/2018 - 12:39
will that work on excel as well?
Excelchat Expert
18/05/2018 - 12:40
Yes it will as long as the references are accurate and the data matches.
User
18/05/2018 - 12:40
ive change the one blue to red yet it still counts 3?
User
18/05/2018 - 12:41
cool
Excelchat Expert
18/05/2018 - 12:41
Because there are still 3. You have been highlighting incorrect records.
User
18/05/2018 - 12:41
thanks you very much
Excelchat Expert
18/05/2018 - 12:41
You were highlighting Tue
Excelchat Expert
18/05/2018 - 12:42
IF you want, you can simply change Mon to Tue in the formula.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.