counting cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Boenerge - 17 Dec 2007 22:11 GMT

What fromula do I use if I want a cell to count how many times a number
(time) occurs in a range of cells.
e.g. cells a1 a2 a3
a4 a5
0700-1600 0700-1200 1200-1600 0800-1100
1100-1500
If I wanted the formula to look for how many times 0700 and 0800 occured,
the answer would be 3.
Thanks
in advance
Jason

Reply to this Message

Bernard Liengme - 17 Dec 2007 23:27 GMT

This counts the 0700:
=SUMPRODUCT(--(LEFT(A1:A5,4)="0700"))
This counts 00700 and 0800
=SUMPRODUCT(--(LEFT(A1:A5,4)={"0700","0800"}))
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> What fromula do I use if I want a cell to count how many times a number
> (time) occurs in a range of cells.
[quoted text clipped - 7 lines]
> in advance
> Jason

Reply to this Message

Ron Coderre - 17 Dec 2007 23:51 GMT

Try this:

For 0700:
=COUNTIF(A1:E1,"0700*")

For 0800:
=COUNTIF(A1:E1,"0800*")

For both:
=SUM(COUNTIF(A1:E1,{"0700*","0800*"}))

Those formula return 2, 1, and 3, respectively.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> What fromula do I use if I want a cell to count how many times a number
> (time) occurs in a range of cells.
[quoted text clipped - 7 lines]
> in advance
> Jason

Reply to this Message