May 14 2021 11:05 AM
Hi,
I can't find an answer for this. I am trying to get a count between two time ranges and I can't get it to work as it should.
I have times in two columns:
Start time End Time
00:01 01:00
01:01 02:00
02:01 03:00
And so on
The problem I am having I can't get the countifs formula to include the end time in the count. If the time in the main sheet is 01:00 for example then the count will come up as 0.
The formula I am using is:
TIFS('Main sheet'!$D$2:$D$10,">="& A3,'Main sheet'!$D$2:$D$10,"<="& B3)
I have attached the excel workbook if that helps. I am pulling my hair out and I know it'll be something simple that I am not doing!
Thank you in advance.
May 14 2021 11:14 AM
May 14 2021 11:21 AM
May 14 2021 11:27 AM
May 15 2021 04:22 AM
To avoid rounding errors I'd use
=SUMPRODUCT(('Main sheet'!$D$2:$D$10>= A2)*('Main sheet'!$D$2:$D$10<=B2))
That will be more reliable.
May 15 2021 11:35 PM - edited May 17 2021 06:44 AM
Solution@Sergei Baklan wrote: ``To avoid rounding errors I'd use
=SUMPRODUCT(('Main sheet'!$D$2:$D$10>=A2)*('Main sheet'!$D$2:$D$10<=B2))
That will be more reliable.``
Sergei might be thinking of the formatting ("external rounding") defect that affects COUNTIFs et al, notably with times that are accurate to the second.
But the "external rounding" defect has no impact on COUNTIFS with times (and date+times) that are accurate to the minute.
And indeed, the SUMPRODUCT formula suggested above has the same results in this case, when using the same comparison operators (see #1 below), because the problem here is not due to the "external rounding" defect.
Instead, the problem here is the "garden variety" rounding issue, namely: the lack of explicit rounding when calculating MOD(B2,1) in column D of the Main worksheet.
One method of correction is (in D2):
=--TEXT(MOD(B2,1),"h:m")
Copy D2 into D3:D24.
Then the COUNTIFS formulas perform as expected, based on how they are written. (But see #4 below.)
However, there are many other mistakes in the Excel file that might eventually contribute to other counting errors.
-----
1. In the August worksheet, the second condition is "<"&B2, not "<="&B2 as it is in the July worksheet and as stated in the original posting. Presumably, the correction is (in C2):
=COUNTIFS('Main sheet'!$D$11:$D$12, ">=" & A2,
'Main sheet'!$D$11:$D$12, "<=" & B2)
Copy C2 into C3:C25.
-----
2. The Start and End times in columns A and B of the July and August worksheets are not the correct binary values for the displayed times.
And Fill Series (in Excel 2010) does not remedy the problem.
Instead, one method of correction is (in A3):
=--TEXT(A2+"1:0","[h]:m")
Copy A3 in B3. And copy A3:B3 into A4:B25.
"[h]" displays 24 instead of zero. (But see #4 below.)
-----
3. The ranges of the COUNTIFS are tailored to the July'19 and August'19 data in the Main worksheet.
If that is your intent, you might write the following in C2 of the July worksheet, for example:
=COUNTIFS('Main sheet'!$B$2:$B$100, ">=" & DATE(2019,7,1),
'Main sheet'!$B$2:$B$100, "<" & DATE(2019,8,1),
'Main sheet'!$D$2:$D$100, ">=" & A2,
'Main sheet'!$D$2:$D$100, "<=" & B2)
But if you want to count all values in July of all years, for example, you might write the following in C2:
=SUMPRODUCT((MONTH('Main sheet'!$B$2:$B$100)=7)
* ('Main sheet'!$D$2:$D$100>=A2)
* ('Main sheet'!$D$2:$D$100<=B2))
Here, we use SUMPRODUCT not because it is more reliable, but because it has functionality that is lacking in COUNTIFS, namely bona fide arrays, not just cell ranges.
Copy C2 into C3:C25.
-----
4. You make mixed assumptions about the interpretation of the time 00:00 (midnight).
MOD(B2,1) in D2 of the Main worksheet (zero) will never be counted because it does not match 24:00 in B25 of the July and August worksheets, which is formatted to display 0:00.
In fact, midnight is the beginning of a day, not the end of a day. So I would suggest the following changes.
a. Change just A2 of the July and August worksheets to 0:00, instead of 0:01.
b. With the changes in #2 and #4a, change the corrected formula in just A3 of the July and August worksheets to:
=--TEXT(A2+"1:1","[h]:m")
Thus, all the other start times 1 min after the hour, as you have now.
c. Change the format of A2:A25 and B2:B25 of the July and August worksheets to Custom [h]:mm.
Thus, B25 displays 24:00, not 0:00.
-----
All of these changes are implemented in the attached Excel file.
May 17 2021 04:43 PM
You are an absolute life saver!
I applied all the logic you stated in your post in my actual spreadsheet and it all worked out how it should!
I can't thank you enough.
May 15 2021 11:35 PM - edited May 17 2021 06:44 AM
Solution@Sergei Baklan wrote: ``To avoid rounding errors I'd use
=SUMPRODUCT(('Main sheet'!$D$2:$D$10>=A2)*('Main sheet'!$D$2:$D$10<=B2))
That will be more reliable.``
Sergei might be thinking of the formatting ("external rounding") defect that affects COUNTIFs et al, notably with times that are accurate to the second.
But the "external rounding" defect has no impact on COUNTIFS with times (and date+times) that are accurate to the minute.
And indeed, the SUMPRODUCT formula suggested above has the same results in this case, when using the same comparison operators (see #1 below), because the problem here is not due to the "external rounding" defect.
Instead, the problem here is the "garden variety" rounding issue, namely: the lack of explicit rounding when calculating MOD(B2,1) in column D of the Main worksheet.
One method of correction is (in D2):
=--TEXT(MOD(B2,1),"h:m")
Copy D2 into D3:D24.
Then the COUNTIFS formulas perform as expected, based on how they are written. (But see #4 below.)
However, there are many other mistakes in the Excel file that might eventually contribute to other counting errors.
-----
1. In the August worksheet, the second condition is "<"&B2, not "<="&B2 as it is in the July worksheet and as stated in the original posting. Presumably, the correction is (in C2):
=COUNTIFS('Main sheet'!$D$11:$D$12, ">=" & A2,
'Main sheet'!$D$11:$D$12, "<=" & B2)
Copy C2 into C3:C25.
-----
2. The Start and End times in columns A and B of the July and August worksheets are not the correct binary values for the displayed times.
And Fill Series (in Excel 2010) does not remedy the problem.
Instead, one method of correction is (in A3):
=--TEXT(A2+"1:0","[h]:m")
Copy A3 in B3. And copy A3:B3 into A4:B25.
"[h]" displays 24 instead of zero. (But see #4 below.)
-----
3. The ranges of the COUNTIFS are tailored to the July'19 and August'19 data in the Main worksheet.
If that is your intent, you might write the following in C2 of the July worksheet, for example:
=COUNTIFS('Main sheet'!$B$2:$B$100, ">=" & DATE(2019,7,1),
'Main sheet'!$B$2:$B$100, "<" & DATE(2019,8,1),
'Main sheet'!$D$2:$D$100, ">=" & A2,
'Main sheet'!$D$2:$D$100, "<=" & B2)
But if you want to count all values in July of all years, for example, you might write the following in C2:
=SUMPRODUCT((MONTH('Main sheet'!$B$2:$B$100)=7)
* ('Main sheet'!$D$2:$D$100>=A2)
* ('Main sheet'!$D$2:$D$100<=B2))
Here, we use SUMPRODUCT not because it is more reliable, but because it has functionality that is lacking in COUNTIFS, namely bona fide arrays, not just cell ranges.
Copy C2 into C3:C25.
-----
4. You make mixed assumptions about the interpretation of the time 00:00 (midnight).
MOD(B2,1) in D2 of the Main worksheet (zero) will never be counted because it does not match 24:00 in B25 of the July and August worksheets, which is formatted to display 0:00.
In fact, midnight is the beginning of a day, not the end of a day. So I would suggest the following changes.
a. Change just A2 of the July and August worksheets to 0:00, instead of 0:01.
b. With the changes in #2 and #4a, change the corrected formula in just A3 of the July and August worksheets to:
=--TEXT(A2+"1:1","[h]:m")
Thus, all the other start times 1 min after the hour, as you have now.
c. Change the format of A2:A25 and B2:B25 of the July and August worksheets to Custom [h]:mm.
Thus, B25 displays 24:00, not 0:00.
-----
All of these changes are implemented in the attached Excel file.