Calculating % of time that values fall within threshold

[greyscale]

SOLVED. cannot use countifs on my version .. using sumproduct instead

Sorry. The format got knocked out. I have attached a file instead

Hi. I have multiple columns of dates with values expressed as a percentage with some rows being blank as no calls were received during that time period. e.g.
1/11 2/11
12:00 AM 0 100
12:15 AM 67 60
12:30 AM 50 100
12:45 AM 100 75
1:00 AM 100
1:15 AM 100 100
1:30 AM
1:45 AM 50 40
2:00 AM 100
2:15 AM 100
In the last row, i need to calculate what percentage of time the rows above fell within a particular band (e.g. 65%-75%), ignoring blank cells. So the calculation for column B would be 16.6% as there was only 1 instance out of 6 where we fell within that band. Can anyone advise me of the calculation I would use? thank you in advance

[DonkeyOte]

SOLVED. cannot use countifs on my version .. using sumproduct instead

FWIW, if the values in the table are numbers (in your sample they appear to be numbers stored as text) then you can still use a COUNTIF based approach:

Please Login or Register  to view this content.

depending on the size of your range and no. of calcs that might prove preferable.

If you do need to coerce the data in rows 2:11 as part of the summary calc then SUMPRODUCT is nec. (or Array equiv.)

[greyscale]

Thanks DonkeyOte. I don't think I can use COUNTIFS in 2003? I will keep the calc you sent me for future reference though. Good to know

[DonkeyOte]

Correct but it's a typo on my part - it should read COUNTIF (not COUNTIFS) - I've since modified.