How to count items?

  • Thread starter Thread starter exceldummy
  • Start date Start date
E

exceldummy

Hi! I don't know how to word this, but let me try....

My sample data:
High, 00:00:04
High, 00:10:25
High, 00:13:45
Medium, 03:41:44
Medium, 21:11:45
Low, 2:00:01
Low, 4:00:04

I want to count the number of High's that's less than 1 hour, less than
8 hours, and more than 8 hours. And then the same for Medium as well
as Low.

Maybe I've phrased it wrongly but I can't find anything around.
Help!!!! I'm so stupid!:(
 
exceldummy said:
Hi! I don't know how to word this, but let me try....

My sample data:
High, 00:00:04
High, 00:10:25
High, 00:13:45
Medium, 03:41:44
Medium, 21:11:45
Low, 2:00:01
Low, 4:00:04

I want to count the number of High's that's less than 1 hour, less than
8 hours, and more than 8 hours. And then the same for Medium as well
as Low.

Maybe I've phrased it wrongly but I can't find anything around.
Help!!!! I'm so stupid!:(
Click to expand...

Try formulas such as this:
=SUMPRODUCT((A1:A7="High")*(B1:B7<TIME(1,0,0)))

Perhaps better would be to have the comparison values in cells, such as
"High" in C1 and 1:00:00 in D1, and use:
=SUMPRODUCT((A1:A7=C1)*(B1:B7<D1))
 
exceldummy said:
It doesn't work. Do you need any add-in for this to work?
Click to expand...

In what way doesn't it work? Does Excel not accept the formula at all? Does
Excel return error values? Does Excel simply return the wrong value?

Details are always useful if not essential.

The formulas in Paul's response should work unless there are details you've
neglected to mention. No add-ins needed.
 
Back
Top