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

##### Active Member
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### FDibbins

##### Well-known Member
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?

##### Active Member
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

#### FDibbins

##### Well-known Member
What do you mean by "decimal"? As I explained, time is a decimal of 1

##### Active Member
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:

##### MrExcel MVP
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>

#### FDibbins

##### Well-known Member
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")

Replies
2
Views
342
Replies
3
Views
876
Replies
4
Views
654
Replies
0
Views
3K
Replies
4
Views
2K

1,191,134
Messages
5,984,864
Members
439,921
Latest member
Neocold

### 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.

### Which adblocker are you using?

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

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