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

  • Matthew Emrich
    Matthew Emrich ✭✭✭✭✭
    edited 06/23/21 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!