SUMIF Formula with Text

[collegeitdept]

I have a list a store locations and their inventory of their freezers (how many). Column A lists the store locations, Column B lists their freezers.

I need a formula (I was thinking of using the SUMIF formula...but it doesn't work with text) in cell G1 that sums how many freezer doors for the store in question (cell E1).

EX: Store 126 has 17 total freezers.

HOWEVER: if you look at Store 130 it lists one of their freezers as "Large Full Freezer"... this is equal to 13.

So if the store in question was 130, the formula would add the freezers and it would be "LF - 25" because it has a Large Full freezer.

Thanks.

 

[collegeitdept]

Sadly, I cannot add a Helper column...

Can you incorporate your formula with the sumif formula...

(ie: i only have one cell to add a formula too... and its in another template file).

Thanks.

 

[Jonmo1]

Sadly, I cannot add a Helper column...

That is sad....
Why not? Company Policy?

Stand up and be the Excel Guru..

"To accomplish this goal, here's what we need to do..."

 

[barry houdini]

On the data you show this should work....

=SUMPRODUCT((A2:A28=E1)*((B2:B28="Large Full Freezer")*13+(0&TRIM(SUBSTITUTE(SUBSTITUTE(B2:B28,"DOOR FREEZER",""),"LARGE FULL FREEZER","")))))

 

[collegeitdept]

Yes its company policy... I have a template file with a designated cell for the formula that will pull the data from the data file.

 

[collegeitdept]

Thank you so very much!!!

 

[Jonmo1]

Nice One Barry...

ray:

 

[collegeitdept]

SO its not working... apparently there were other text variables...

so its throwing an error in the formula... can you amend the formula and tell it to ignore all other text variables while keeping the previous the formula the same?

here is the <S>FULL</S> data list:

No, here is a REPRESENTATIVE sample - 2,500 rows of data is not needed to accomplish your task - Moderator.

3021 Large Full Freezer
3077 Large Full Freezer
2021 Large Full Freezer
558 CONDENSING UNIT
1802 COMPRESSOR UNIT
1201 Large Full Freezer
417 3 DOOR FREEZER
507 3 DOOR FREEZER
552 2 DOOR FREEZER
3108 3 DOOR FREEZER
458 Reach In Freezer
414 3 DOOR FREEZER
1104 EVAP COIL
2224 FREEZER
3201 REACH IN
3207 2 DOOR FREEZER

 

[collegeitdept]

The other text variables are:

558 CONDENSING UNIT
1802 COMPRESSOR UNIT
458 Reach In Freezer
1301 WI COND
1807 CONDENSER
1811
1812 CHEST FREEZER
1812 COMPRESSOR UNIT
1104 CONDENSER
1805 CONDENSER
1104 EVAP COIL
2224 FREEZER
3201 REACH IN



Can you tell the formula to ignore these other variables or give the text variable a value of 3?

Thanks.

 

[collegeitdept]

btw... i was scrolling through the list... some store locations are misspelled with their freezers.

most say # DOOR FREEZER

a few of them say: # DOOR FREEZERS

one says: # DDOOR FREEZER

but in all, the formula should simply look at the first 2 characters for the number value (the number will never go beyond 2 digits)

Thanks.

 

[collegeitdept]

One says: " DDOR FREEZER"