Issue with countifs statement when there's an if statement
My countifs statement is this
=COUNTIFS({Tech 1}, $Tech@row, {Start 1}, <=[6.00]$1, {End 1}, >[6.00]$1)
and the formula in the column it's looking in is this
=IF(AND(Split@row = 1, [Required Staff]@row > 1), IFERROR(LEFT([Tech 1 Hours]@row, FIND("-", [Tech 1 Hours]@row) - 1), [Start Time]@row), [Start Time]@row)
As long as the split value is =0, the countifs works, but if the split value is 1, it doesn't count it even if all the conditions are met
Am I missing something?
Best Answer
-
Figured it out, One of the values was a text the other was a number. I had to convert the start and end columns to numbers using the values function.
Corrected Start Time Formula
=IF(AND(Split@row = 1, [Required Staff]@row > 1), IFERROR(VALUE(LEFT([Tech 1 Hours]@row, FIND("-", [Tech 1 Hours]@row) - 1)), [Start Time]@row), [Start Time]@row)
Corrected End Time Formula
=IF(AND(Split@row = 0, [Required Staff]@row > 1), [End Time]@row, IF(CONTAINS("-", [Tech 1 Hours]@row), IFERROR(VALUE(RIGHT([Tech 1 Hours]@row, 5)), [End Time]@row)))
😀
@Paul Newcome Thanks for looking at it
Answers
-
What is in each the columns referenced in this portion?
IFERROR(LEFT([Tech 1 Hours]@row, FIND("-", [Tech 1 Hours]@row) - 1), [Start Time]@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It is a time range of start and end times. For example 9.00-10.00. So the value I pull out of that equation is the start time of an activity.
for the end time, I have this
=IF(Split@row = 1, IFERROR(LEFT(SUBSTITUTE([Tech 1 Hours]@row + "-", JOIN([Tech 1 Start]@row:[Tech 1 Start]@row, "-") + "-", ""), FIND("-", SUBSTITUTE([Tech 1 Hours]@row + "-", JOIN([Tech 1 Start]@row:[Tech 1 Start]@row, "-") + "-", "")) - 1), [End Time]@row), [End Time]@row)
-
This is the main sheet with the information
This is the sheet with the countifs
Right now it only is counting the rows where split is not checked. The idea of the split is that if a time point is from 8 to 10, we can have 1 person do 8 to 9 and the other do 9 to 10. We would input these times into the Tech hours columns as 8.00-9.00 & 9.00-10.00. Theses then would get split up in the tech start and end time columns which is what is used in the formula. If it's not split, both people are working 8 to 10.
When I uncheck the column, the data of the non split start and end time is calculated.
-
I went even simpler and tested this formula with a cell that had a value of 10.00
=COUNTIF({start}, 10.00)
The result I got was 0
-
Exactly how is the [Start Time] column populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Figured it out, One of the values was a text the other was a number. I had to convert the start and end columns to numbers using the values function.
Corrected Start Time Formula
=IF(AND(Split@row = 1, [Required Staff]@row > 1), IFERROR(VALUE(LEFT([Tech 1 Hours]@row, FIND("-", [Tech 1 Hours]@row) - 1)), [Start Time]@row), [Start Time]@row)
Corrected End Time Formula
=IF(AND(Split@row = 0, [Required Staff]@row > 1), [End Time]@row, IF(CONTAINS("-", [Tech 1 Hours]@row), IFERROR(VALUE(RIGHT([Tech 1 Hours]@row, 5)), [End Time]@row)))
😀
@Paul Newcome Thanks for looking at it
-
That's what I was thinking it would end up being. Glad you were able to figure it out.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.7K Get Help
- 371 Global Discussions
- 205 Industry Talk
- 436 Announcements
- 4.5K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 129 Community Job Board
- 448 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!