EXCEL 2003 Countif formula problem with combined Times and Decimal Numbers in same Row.

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
Hello all,

I am having trouble with countif Function as described below.

Sorry MrExcleHTML is not working.

In the below cells, the following formulas and results are applied as below:

C67 =COUNTIF(K67:AA67,"<=5") results are 6 Should be 0
E67 =COUNTIF(K67:AA67,">=5.1") results are 11 Should be 1
G67 =COUNTIF(K67:AA67,">=8.1") results are 10 Should be 8

Entered Data
All Times are formatted as Custom hh:mm
All Numbers are formatted as Number 1 Decimal Place
J67 5:29 AM
K67 10.4
L67 7:06 AM
M67 12.6
N67 8:45 AM
O67 18.9
P67 9:45 AM
Q67 23.3
R67 12:31 PM
S67 7.9
T67 13:31
U67 9.8
V67 14:24
W67 12.9
C67 15:24
Y67 12.8
Z67 22:32
AA67 12.2

Do not want to move Times above Numbers entered.
Any help would be greatly appreciated.

Paul
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Wed 28 Dec 2016) is actually 42732

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

With that said, what exactly are you trying to count?
 
Upvote 0
Hi FDibbins ,

I am trying to get the results of the decimal numbers ONLY excluding the Times in that same row. I have tried Non-Contingious cells but there is a limit of 7 I think.

The criteria for the Decimal Numbers are as follows:

<= 5 low
>=5.1 or <=7.9
=> 8 hi

Thanks Paul
 
Upvote 0
What do you mean by "decimal"? As I explained, time is a decimal of 1

Please give sample answer and how you got that
 
Upvote 0
Hello FDibbins,

The Decimal Numbers come from a Blood Glucose Meter reading. I enter the numbers as seen from the meter, and format those cells as Number 1 Decimal Place.
In each of the Glucose Reading I enter I include the time it was taken. I can get the correct formula intended, if I were to place the time above the reading. I would rather not, Just wondering if there is a formula I can use that will only display the results for the numbers enter excluding the Times.

As mentioned earlier the Criteria I am trying to analyse is:
The criteria for the Decimal Numbers are as follows:

Display the actual Counts of the reading numbers that occur in this range.
<= 5 low
>=5.1 or <=7.9 Within Range
=> 8 hi
 
Last edited:
Upvote 0
Since time values and decimals occur at every other column...

1.

=SUMPRODUCT(--(MOD(COLUMN(J67:AA67)-COLUMN(J67)+1,2)=0),--(J67:AA67<=5))

2.

<strike></strike>=SUMPRODUCT(--(MOD(COLUMN(J67:AA67)-COLUMN(J67)+1,2)=0),--(J67:AA67>=5.1))

3.

<strike></strike>=SUMPRODUCT(--(MOD(COLUMN(J67:AA67)-COLUMN(J67)+1,2)=0),--(J67:AA67>=8.1))
<strike></strike>
 
Upvote 0
Try this...
C​
D​
E​
67​
0​
1​
8​
C67=COUNTIFS($J67:$AA67,">1",$J67:$AA67,"<=5")
D67=COUNTIFS($J67:$AA67,">1",$J67:$AA67,">=5.1",$J67:$AA67,"<8")
E67=COUNTIFS($J67:$AA67,">1",$J67:$AA67,">=8")
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top