How to write an excel index match formula with varying criteria?

[Kelmike]

Good day,

I have this workbook with 3 sheets (Data, 1st and 2nd). In Data, i have a table with index match formulas to auto populate the values
of each HBins (1~12). So far it was working for most of the time but then I found out that some search pattern I'm using were changing,
hence, instead of returning the correct value, it just shows 0 since my search string does not match anymore. Is there a way I can make my
formula adapt to this varying search patterns? Im thinking something like an OR function. Would that be possible?

Here are samples of the different search strings for HBIN 1. This is almost the same for the other HBINs, the number just change:

Enter Good Bin Qty
Good Bin 1
Hardbin 1
Hardbin1
HBin1

My current pattern will just match "Enter BIN 1 Quantity" and ENTER BIN 1 QTY", but not "Enter BIN1 Quantity".

Appreciate the help you may extend, thanks a lot in advance.

 

[jasonb75]

Sumifs on its own can only check each cell once, so you would be able to check for those with a space or those without, but not both. By using an array for the criteria you can create 2 sumifs formulas in one, then enclosing it in sum adds them together.

It is just a shorter way of writing =SUMIFS('2nd'!$D:$D,'2nd'!$B:$B,"BIN1 Q*")+SUMIFS('2nd'!$D:$D,'2nd'!$B:$B,"BIN 1 Q*")

Personally, I would use this in E6, then fill down so that you don't have to edit each formula manually.

=SUM(SUMIFS('2nd'!$D:$D,'2nd'!$B:$B,"*BIN"&{"",""," "," "}&ROWS(E$6:E6)&{""," ",""," "}&"Q*"))

This will find anything that contains the text "BIN#Q", with and without a space both before and after the bin number without the risk of the bins getting mixed. "Good Bin", "HARDBIN", "HBIN" etc. will all be counted.

 

[Kelmike]

Sumifs on its own can only check each cell once, so you would be able to check for those with a space or those without, but not both. By using an array for the criteria you can create 2 sumifs formulas in one, then enclosing it in sum adds them together.

It is just a shorter way of writing =SUMIFS('2nd'!$D:$D,'2nd'!$B:$B,"BIN1 Q*")+SUMIFS('2nd'!$D:$D,'2nd'!$B:$B,"BIN 1 Q*")

Personally, I would use this in E6, then fill down so that you don't have to edit each formula manually.

=SUM(SUMIFS('2nd'!$D:$D,'2nd'!$B:$B,"*BIN"&{"",""," "," "}&ROWS(E$6:E6)&{""," ",""," "}&"Q*"))

This will find anything that contains the text "BIN#Q", with and without a space both before and after the bin number without the risk of the bins getting mixed. "Good Bin", "HARDBIN", "HBIN" etc. will all be counted.

Hi @jasonb75, thanks a lot for the help and explanation, really appreciate it. have a nice day and pls dont stop helping others like me, thanks again.