Formula Help Countifs and Match

Matthew Emrich

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

Genevieve P.

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!

Cheers,

Genevieve

Bassam Khalil

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

Matthew Emrich

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

Genevieve P.

Hi @Matthew Emrich

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?

Matthew Emrich

@Genevieve P

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

Genevieve P.

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!

Cheers,

Genevieve

Matthew Emrich

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

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

Genevieve P.

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

Bassam Khalil

@Matthew Emrich 

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

Susan Vieira

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

Genevieve P.

Hi @Susan Vieira

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?

Susan Vieira

@Genevieve P.

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

Genevieve P.

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

Susan Vieira

Yes, got it! Thanks again.