counting cells

[Boenerge]

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

 

[Bernard Liengme]

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

 

[Ron Coderre]

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)