Formula Help Countifs and Match

05/10/21
Accepted

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}, >[email protected], {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).

Popular Tags:

Best Answer

  • Genevieve PGenevieve P admin
    Accepted 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}, >[email protected], {EDBDate}, <=[Last Date]@row) > 0, "Error")


    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Bassam KhalilBassam 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: [email protected]

    Best Regards

    Bassam.M Khalil


    ☑️ 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.

  • 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?

  • @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 PGenevieve P admin
    Accepted 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}, >[email protected], {EDBDate}, <=[Last Date]@row) > 0, "Error")


    Let me know if this works for you!

    Cheers,

    Genevieve

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

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

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

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    edited 05/16/21

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

    Best Regards

    Bassam.M Khalil


    ☑️ 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". 

Sign In or Register to comment.