# 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")

SHEET A

SHEET B

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

Tags:

• Employee

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!

Cheers,

Genevieve

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭

Hi @Matthew Emrich

Hope you are fine if it's possible to create a workspace and save a copy of the two sheets with sample data and share me as admin on that workspace i will create the exact formula for you.

My Email: Bassam.k@mobilproject.it

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

• ✭✭✭✭✭

@Bassam Khalil Access given. Thank you for the help.

• Employee

I believe part of the issue here is that you're looking to match a Multi-Select column against cells with a single value. For example, since Fob & Pumps are both selected in one cell, it won't be able to find any Matches in your Equipment Total top row, since each cell holds one of these values (either Fob or Pumps). The Match function will read the two values as one combination value, does that make sense?

How many Equipment possibilities are there, and could you change this to either be a single-select column or multiple columns identifying other choices?

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭

It should have been a single-select column, it was accidently changed to multi-select when I was trying out something and I forgot to change it back

• Employee

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!

Cheers,

Genevieve

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭

@Genevieve P Thank you so much. That solved it.

@Bassam Khalil Problem was solved, thanks for looking into it

• Employee

Great! Thanks for letting me know it worked out. 🙂

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
edited 05/16/21

Am sorry for the delay but i was infected with Covid-19, thanks for @Genevieve P She is always creative in her answers.

bassam.khalil2009@gmail.com

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

• ✭✭✭✭✭

@Genevieve P. This works great but I don't understand all the elements of it. Can I ask you for further clarification on this formula? The row_index is 0 before the MATCH statement. I don't understand the function of the 0. Does the 0 basically say look at all rows? As opposed to a specific row? Or is it looking at column headers? Thanks so much for your continued help with this.

• Employee

No problem! Yes you are right, the 0 means that the formula will look through all the rows, and then the MATCH portion is how it identifies what column to pull from.

INDEX(range, row_index, column_index)

Does that help?

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭

Yes it does, thank you! So if I am understanding correctly, MATCH reads the Column names?

• Employee

The MATCH will read the first row, which is where your column name will need to be duplicated. (See the blue row in Sheet B above.)

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭

Yes, got it! Thanks again.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!