Counting data

[rekki06]

Hey,

I am a big novice when it comes to Excel and I am trying to make a formula to count a few bits of data.

What I need is for the formula to look at column F and count a specific time range i.e if any times displayed are between 9am-12pm. The extra condition is in Colum F there will be either a number 1 or a number 3.

The end result would to have 2 boxes that count the amount of inputs in F between 9-12 compared to what is displayed in F.


An example of the data inputted would be;

F --------- H
09:00----- 1
09:18----- 3
10:25------1
10:36----- 1
11:34----- 3


The end result - 1 = 3
--------------------- 3 = 2

Would this be possible?

Thank you in advance for your reply's.

[AliGW]

Just use a COUNTIFS statement - add a criterion for each of the times (start and end) and one for the column H criterion.

Excel 2016 (Windows) 32 bit

	A	B
1	F	H
2	09:00	1
3	09:18	3
4	10:25	1
5	10:36	1
6	11:34	3

Sheet: Sheet1

For the ones:

=COUNTIFS($A$2:$A$6,">="&TIME(9,0,0),$A$2:$A$6,"<="&TIME(12,0,0),$B$2:$B$6,1)

For the threes:

=COUNTIFS($A$2:$A$6,">="&TIME(9,0,0),$A$2:$A$6,"<="&TIME(12,0,0),$B$2:$B$6,3)

[AliGW]

By the way, are you still using Excel 2010? If not, then please update your user profile to show which version you are now using.

[rekki06]

Thank you so much.

This has worked and is going to make things so much more easier.


Thanks for your prompt replies

[AliGW]

No worries - please update your profile as requested, if appropriate.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.