# how to use Countifs function Excel?

from what i have been reading online it appears Countifs function is what i need to use for my problem. I have data that looks like this and goes on for several months:

``````  |Month    |Day|   Time           |Value
|  May    |1  |7:30 AM - 9:30 AM |2
|  May    |1  |9:30 AM - 12:30 PM|2
|  May    |1  |12:30 PM -3:00 PM |2
|  May    |1  |3:00 PM - 5:30 PM |1
|  May    |1  |5:30PM  - 7:00 PM |3
|  May    |1  |7:00 PM - 11:00 PM|1
``````

for each month, I want it to count how many times the value is 1 for each time interval, 2 for each time interval and 3 for each time interval. it seems that countifs is appropriate for the logic of doing this. Ive never used countifs and I am unsure how to write it, if countifs is the right function.

• You can try reading this – cybernetic.nomad Aug 26 '18 at 16:37
• What's the result table do you need? Could you provide a result table about your problem? Based on your description, in my opinion COUNTIF and SUMPORDUCT can get your result. But I don't know how to provide the formula about your problem. – Lee Aug 27 '18 at 8:31

Actually I think what you want is a Pivot Table. If I understand you correctly, with your given data set you want results that look like:

``````+-----------------------+-----------+
| Time interval         | ValueCount|
|                       | 1 | 2 | 3 |
+-----------------------+---+---+---+
| 7:30 AM - 9:30 AM     | 0 | 1 | 0 |
| 9:30 AM - 12:30 PM    | 0 | 1 | 0 |
| 12:30 PM -3:00 PM     | 0 | 1 | 0 |
| 3:00 PM - 5:30 PM     | 1 | 0 | 0 |
| 5:30PM  - 7:00 PM     | 0 | 0 | 1 |
| 7:00 PM - 11:00 PM    | 1 | 0 | 0 |
+-----------------------+---+---+---+
``````

Then that's a pivot table. I doctored up some random data with values 1-10: I'm working on a Mac so my Excel may look slightly different, but it should be basically the same.

Look for Insert > Pivot Table. In the pivot table I set the `Rows` to be the `Time` column, the `Columns` to be the `Value` column, and the `Values` to be the `Count of Value` (it defaults to Sum).

• Your right, I thought of pivot table, but I couldn't figure out how to do it. I actually did what I needed with countifs, but this is better. I will change it to pivot table when I'm at work. Thank you. – Terry Aug 27 '18 at 13:50