Formula Help Countifs and Match

I'm trying to develop a cross sheet formula that will give me an error message if I've over-allocated equipment. This is What I've come up with so far but I get Incorrect argument.

=IF(COUNTIFS(MATCH([Equipment Required]@row, {Equipment Total}, 1), >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row) > 0, "Error")



The idea is to search through Equipment Total Row on SHEET B for the Equipment Required value from SHEET A, in order to know which column to look for the data in. Once the correct column is identified, It will look through that column for the number of times the value in the column is greater than the Equipment Max on Sheet A. It will then count all the times the dates on SHEET B fall between the Date range on SHEET A (Start and Last Date).


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Matthew Emrich

    Thanks for clarifying! Ok so I've tested this on my own sheet and I think I know where you're getting stuck.

    Instead of first jumping into the MATCH, you'll actually need to use an INDEX(MATCH to find the Column based on the Equipment Required.

    An INDEX function works like this:

    INDEX(range to Evaluate, Row_Index, Column_Index)

    In your case, the column index is based on the MATCHing Equipment. This means your INDEX should look like this:

    INDEX({Full Range}, 0, MATCH([Equipment Required]@row, {Full Range}, 0))

    *Note that my {Full Range} is not just the first row in your Sheet B, but the entire columns in that sheet.

    Try this:

    =IF(COUNTIFS(INDEX({Full Range}, 0, MATCH([Equipment Required]@row, {Full Range}, 0)), >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row) > 0, "Error")

    Let me know if this works for you!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!