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.

 

[barry houdini]

If you want to do that with one formula you might need to switch to an "array formula" like this

=SUM(IF(A2:A28=E1,IF(ISNUMBER(LEFT(B2:B28)+0),LEFT(B2:B28,FIND(" ",B2:B28)-1)+0,IF(B2:B28="Large Full Freezer",13,IF(B2:B28<>"",3)))))

That should add the numbers for those that have them and count 13 for a "Large full freezer" and 3 for any other text (not blanks)

The formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

 

[collegeitdept]

The formula doesn't work


the list goes on until the 2500 row so i changed the formula to:

=SUM(IF(A2:A2500=E1,IF(ISNUMBER(LEFT(B2:B2500)+0),LEFT(B2:B28,FIND(" ",B2:B2500)-1)+0,IF(B2:B2500="Large full freezer",13,IF(B2:B2500<>"",3)))))


it comes up with "#N/A"

 

[barry houdini]

You still have one range unchanged - try like this

=SUM(IF(A2:A2500=E1,IF(ISNUMBER(LEFT(B2:B2500)+0),LEFT(B2:B2500,FIND(" ",B2:B2500)-1)+0,IF(B2:B2500="Large full freezer",13,IF(B2:B2500<>"",3)))))

 

[collegeitdept]

Sorry... it works!!!

forgot to hit CTL + SHIFT + ENTER

THanks so much!!!

 

[collegeitdept]

On the cell next to it... how do show a warning if it's a "large full freezer"?

ie: 435 has a large full freezer... so the if formula would return "LF" and if the store number had a special (another text variable ie: reach in, compressor, etc...) the formula would return "CK" to check that store and get proper information.

Thanks.

 

[collegeitdept]

For all other stores with proper information ("# DOOR FREEZER") it would return FALSE or ""

 

[barry houdini]

Try this version

=IF(SUMPRODUCT((A2:A2500=E1)*(B2:B2500="Large full freezer")),"LF",IF(SUMPRODUCT((A2:A2500=E1)*(ISERR(LEFT(B2:B2500)+0))),"CK",""))