how to use Countifs function Excel?

Question

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.

Answer 1

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).