0

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
0

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: Excel pivot table 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.