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.

 

[Norie]

Eh, that's just untrue - SUMIF will work with text, I don't know where you got the idea it wouldn't.

I'm also a little confused - how does LARGE FULL FREEZER equate to 13?

 

[Jonmo1]

I would add a helper column, Say in C2, put a formula
=IF(ISNUMBER(LEFT(B2,1)+0),LEFT(B2,1)+0,0)

Fill it down.

Now you can use sumif, summing column C if A = 126


It can be modified if you will ever have 2 or 3 digit numbers in the column B Text..

 

[collegeitdept]

For our company it Large Full Freezer equals thirteen.

But do you have a formula for this (and with this "large full freezer" condition)?

Thanks.

 

[Norie]

There is nothing in the data you've posted that mentions/indicates that 13 is related with LARGE FULL FREEZER.

Do you have another worksheet with more data?

 

[collegeitdept]

I actually can't modify the spreadsheet.... the formula will actually go in one of my Excel files... the data list file is read-only stored on the network.

 

[Jonmo1]

I think what Norie's getting is..

If it's only this one thing "Large Full Freezer" = 13, it can be accounted for.
But do you have lots of things like that?
Like, Large Refrigerator = 7
Large Oven = 4
Etc...

If you are litterally only working with this posted data, Freezers, and Large Full Freezer is the ONLY exception, it's doable.

 

[collegeitdept]

No... Its just company policy that "large full freezer" equals 13.

Can you work this in the formula ie (=if(cell,"large full freezer",13,0)??

Thanks.

 

[collegeitdept]

"Large Full Freezer" is the only text variable in the spreadsheet.


Thank you all so much for your help.

 

[Jonmo1]

So you're not going to come back later and say...

"OK, I also need to say Large Refrigerator = 12, and Medium Oven = 7"

Right???

going back to the Helper column, put this in C2 and fill down.

=IF(B2="Large Full Freezer",13,IF(ISNUMBER(LEFT(B2,1)+0),LEFT(B2,1)+0,0))

Then do your sumif on column C