SUMIF Formula with Text

collegeitdept

Board Regular
Joined
Nov 14, 2008
Messages
185
4423508734_a2c53e0787_o.jpg


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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Nov 14, 2008
Messages
185
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 14, 2008
Messages
185
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

Board Regular
Joined
Nov 14, 2008
Messages
185
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

Board Regular
Joined
Nov 14, 2008
Messages
185
"Large Full Freezer" is the only text variable in the spreadsheet.


Thank you all so much for your help.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Forum statistics

Threads
1,144,374
Messages
5,723,992
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top