SOLVED

IF Function Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2737249%22%20slang%3D%22en-US%22%3EIF%20Function%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2737249%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbout%208%20months%20ago%20I%20created%20a%20basic%20excel%20spreadsheet%20to%20keep%20up%20with%20trailers%20I%20work%20with%20at%20work.%20My%20job%20is%20an%20administrator%20for%20Walmart%20who%20tracks%20Shipped%20and%20Hold%20trailers%20to%20our%20service%20centers.%20I%20completely%20overhauled%20the%20hand%20and%20paper%20process%20I%20initially%20learned%20and%20fully%20integrated%20my%20spreadsheets%20for%20daily%20use%20and%20my%20supervisors%20love%20it.%20Now%20I%E2%80%99m%20in%20the%20process%20of%20wanting%20to%20modify%20that%20code%20for%20more%20simplicity%2C%20initially%20when%20I%20designed%20it%20months%20ago%20I%20wanted%20the%20design%20to%20be%20like%20this%20but%20I%20couldn%E2%80%99t%20find%20the%20correct%20function.%20Here%20is%20an%20example%20of%20kind%20of%20what%20my%20functions%20are%20set%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20control%205%20different%20batches%20daily%20so%20there%20are%205%20separate%20worksheets%20within%20the%20one%20excel%20document%20.%20Each%20are%20to%20count%20my%20trailers%20if%20(countif)%20a%20trailer%20%26nbsp%3Bships%3B%20so%20my%20current%20setting%20is%20something%20similar%20to%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BCELL%3C%2FP%3E%3CP%3EA1%20B1%20C1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20A1%20would%20hold%20my%20trailer%20number%20ex.%20Trailer%20(123456)%3C%2FP%3E%3CP%3EB1%20would%20hold%20the%20percentage%20ex.%20100SL%3C%2FP%3E%3CP%3EC1%20calculates%20all%20SL%20trailers%20in%20the%20cell%20for%20a%20sum%20total%20at%20end%20of%20each%20shift%20of%20shipped%20trailers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20currently%20I%E2%80%99m%20using%20(COUNTIF%20B1%20%3D%20100SL)%20again%20this%20is%20a%20simple%20upgrade%20from%20the%20hand%20and%20paper%20version%20I%20initially%20learned%20yet%20seeing%20how%20a%20trailer%20can%20be%20shipped%20(SL)%20at%20ANY%20percent%20I%20had%20to%20also%20continue%20the%20countif%20function%20as%20(C%3CSPAN%3EOUNTIF%20B1%20%3D%20100SL%20%2B%20COUNTIF%20B1%20%3D%2090SL%20%2B%20COUNTIF%20B1%20%3D%2080SL%20and%20so%20on)%20this%20is%20ongoing%20for%20the%20entire%2030%2B%20cells%20I%20have%20populated%20to%20work%20with%20this%20function.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat%20I%20want%20to%20know%20is%20there%20a%20function%20than%20is%20more%20simplistic%20to%20this%20that%20I%20can%20use%20base%20off%20of%20just%20the%20signal%20of%20an%20expression%3A%20so%20for%20example%20if%20a%20trailer%20is%20sealed%20at%2080SL%20(or%20whatever%20percent)%20how%20can%20I%20have%20excel%20continue%20to%20count%20anything%20ending%20with%20%E2%80%9CSL%E2%80%9D%20after%20my%20numerical%20value%20is%20placed%20instead%20of%20having%20to%20continue%20using%20a%20string%20of%20nested%20calculation%20for%20different%20values.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2737249%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2737559%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2737559%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1151324%22%20target%3D%22_blank%22%3E%40Optimistic92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20for%20you%20to%20post%20a%20sample%20of%20your%20actual%20spreadsheet%2C%20or%20at%20the%20very%20least%20a%20simple%20mockup%20that%20replicates%20the%20quandary%20you're%20facing%3F%20(Not%20an%20image%2C%20an%20actual%20spreadsheet)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20absence%20of%20that%2C%20or%20if%20you%20would%20prefer%20to%20work%20just%20from%20suggestions--which%20is%20fine--then%20let%20me%20suggest%20the%20following%20possibility%3A%3C%2FP%3E%3CUL%3E%3CLI%3EPlace%20the%20SL%20(or%20any%20other%20codes)%20in%20a%20separate%20column%20from%20the%20percentage%20figures%20that%20you%20are%20counting%20and%20summing.%3C%2FLI%3E%3CLI%3EYou%20can%20then%20use%20%3CSTRONG%3ESUM%3C%2FSTRONG%3E%20or%20%3CSTRONG%3ESUMIF%3C%2FSTRONG%3E%20or%20%3CSTRONG%3ECOUNT%3C%2FSTRONG%3E%20or%20%3CSTRONG%3ECOUNTIF%3C%2FSTRONG%3E%20to%20sum%20or%20count%20the%20numeric%20values%20based%20on%20criteria%20referencing%20the%20separate%20column's%20codes.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EIf%20I%20were%20in%20your%20place%20I'd%20also%20consider%20getting%20rid%20of%20the%20five%20separate%20sheets%2C%20putting%20it%20all%20into%20a%20single%20database%2C%20using%20a%20single%20column%20to%20reflect%20the%20batch.%20And%20yes%2C%20another%20for%20date%2C%20so%20that%20a%20year's%20worth%20(or%20more)%20can%20all%20be%20in%20a%20single%20table.%20You'll%20be%20even%20more%20impressive%20to%20your%20bosses%20with%20how%20you%20can%20tabulate%20comparisons%3A%20%26nbsp%3Bquarter%20to%20quarter%2C%20month%20to%20month%2C%20week%20to%20week.%20Have%20you%20explored%20the%20%3CSTRONG%3EPivot%20Table%3C%2FSTRONG%3E%20feature%3F%20You'll%20love%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2737772%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2737772%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20haven't%20had%20much%20training%20with%20the%20pivot%20tables%20yet%20although%20I'm%20in%20MIS%20graduate%20that's%20nothing%20I%20delved%20into%20yet.%20However%2C%20I%20would%20love%20to%20know%20but%20I've%20attached%20a%20copy%20of%20one%20of%20my%20completed%20shift%20excel%20documents%20to%20give%20you%20an%20idea%20of%20what%20I%20was%20speaking%20about.%20Everything%20within%20the%20document%20from%20tab%20121%2C%20122%2C%20123%2C%20124%2C%20and%20125%20are%20also%20calculated%20on%20the%20last%20EOS%20tab%20(which%20is%20end%20of%20shift%20report%20for%20data%20keeping)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2738015%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1151324%22%20target%3D%22_blank%22%3E%40Optimistic92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%2C%20my%2C%20Jeremy.%20You've%20done%20a%20masterful%20job%20of%20taking%20what%20was%20a%20paper%20process%20and%20converting%20it%20to%20Excel.%20The%20problem%20is%20that%20you've%20done%20just%20that%3A%20taken%26nbsp%3Bwhat%20was%20a%20paper%20process%20and%20converted%20it%20to%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20done%20that--and%20I've%20seen%20this%20happen%20in%20other%20instances%20too--by%20imposing%20that%20paper%20process%20mindset%20onto%20Excel%2C%20rather%20than%20by%20re-conceptualizing%20the%20whole%20process%20as%20a%20database%20which%20can%20take%20advantage%20of%20Excel's%20abilities%20to%20report%20on%20transactions%20such%20as%20movements%20of%20trailers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%20done%20a%20really%20good%20job%2C%20and%20your%20formulas%20that%20count%20dispatched%20trailers%20are%20impressive%20examples%20of%20the%20COUNTIFS%20function.%20And%20how%20you've%20incorporated%20the%20Page%20Footer%20into%20some%20of%20the%20tabs%20so%20as%20to%20note%20such%20anomalies%20as%20%26nbsp%3B%22Must%20Ship%22%20is%20impressive.%20But%20also%20depressing%2C%20in%20that%20you%20literally%20are%20picturing%20this%20as%20a%20page%20of%20a%20report%2C%20with%20a%20footer%20that%20contains--in%20the%20case%20of%20%22Must%20Ship%22%20at%20any%20rate%2C%20something%20that%20should%20be%20its%20own%20column--an%20attribute%20belonging%20with%20a%20store%20code%20on%20the%20database.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20your%20immediate%20problem%2C%20the%20one%20you%20posted%2C%20I%20go%20back%20to%20my%20recommendation%20that%20you%20separate%20the%20SL%20and%20other%20codes%20from%20the%20number%20that%20represents%20a%20percentage.%20I've%20done%20it%20for%20you%20as%20a%20demonstration%2C%20on%20tab%20124%2C%20and%20shown%20two%20examples%20of%20how%20you%20could%20create%20some%20cleaner%20summary%20data%20with%20far%20simpler%20formulas.%20As%20a%20general%20principle%2C%20it%20complicates%20things%20unnecessarily%20to%20combine%20two%20different%20types%20of%20data%20in%20a%20single%20cell%2C%20as%20you've%20been%20doing.%20A%20percent%20PLUS%20a%20code%20for%20disposition%20makes%20it%20hard%20to%20do%20things%20with%20either!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1631219159521.png%22%20style%3D%22width%3A%20703px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309438iB5B9F2FD9DC5476D%2Fimage-dimensions%2F703x311%3Fv%3Dv2%22%20width%3D%22703%22%20height%3D%22311%22%20role%3D%22button%22%20title%3D%22mathetes_0-1631219159521.png%22%20alt%3D%22mathetes_0-1631219159521.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20added%20a%20couple%20other%20suggestions%20in%20text%20boxes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20long%20term%20recommendation%20would%20be%20to%20get%20yourself%20a%20book%20(there%20are%20many%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsmile.amazon.com%2Fs%3Fk%3Dexcel%2Bas%2Bdatabase%26amp%3Bref%3Dnb_sb_noss_2%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsmile.amazon.com%2Fs%3Fk%3Dexcel%2Bas%2Bdatabase%26amp%3Bref%3Dnb_sb_noss_2%3C%2FA%3E%26nbsp%3B)%20on%20creating%20and%20using%20databases%20in%20Excel.%20You're%20clearly%20smart%20enough%20to%20master%20this%2C%20and%20there's%20no%20reason%20why%20you%20need%20to%20take%20a%20course--many%20good%20Excel%20users%20are%20self-taught%20through%20reading%20of%20books%20and%20manuals%20and%20%22playing%22%20with%20what%20they%20find%20there%20in%20meeting%20needs%20at%20work.%20That's%20how%20I%20did%20it%2C%20back%20in%201971%20and%201972%2C%20before%20there%20were%20PCs%20and%20spreadsheets...on%20an%20IBM%20mainframe%20using%20a%20language%20called%20APL.%20Totally%20self-taught.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2738271%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738271%22%20slang%3D%22en-US%22%3EI%20appreciate%20this%3B%20I%20believe%20implementing%20this%20process%20will%20defiantly%20continue%20to%20make%20my%20job%20more%20efficient.%20THANK%20YOU%20SO%20MUCH.%20%26amp%3B%20yes!%20I'm%20an%20advocate%20for%20learning%20anything%20new%2C%20once%20I%20completed%20my%20degree%20and%20the%20concentration%20of%20Management%20of%20Information%20System%20(which%20mainly%20organizes%20data)%20I%20became%20fanatical%20with%20implementing%20my%20learnings%20in%20my%20everyday%20life%2C%20especially%20at%20work%20which%20has%20been%20a%20huge%20help.%20I%20certainly%20appreciate%20your%20recommendation%20and%20I%20had%20initially%20planned%20on%20taking%20a%20course%20in%20EXCEL%20to%20become%20a%20more%20strong%20user%20however%2C%20I%20believe%20I%20apply%20the%20good%20book%20knowledge%20first%20per%20your%20recommendation%20to%20see%20how%20much%20my%20brain%20will%20absorb.%3CBR%20%2F%3EThanks%20Again%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello Excel Community,

 

About 8 months ago I created a basic excel spreadsheet to keep up with trailers I work with at work. My job is an administrator for Walmart who tracks Shipped and Hold trailers to our service centers. I completely overhauled the hand and paper process I initially learned and fully integrated my spreadsheets for daily use and my supervisors love it. Now I’m in the process of wanting to modify that code for more simplicity, initially when I designed it months ago I wanted the design to be like this but I couldn’t find the correct function. Here is an example of kind of what my functions are set to do.

 

i control 5 different batches daily so there are 5 separate worksheets within the one excel document . Each are to count my trailers if (countif) a trailer  ships; so my current setting is something similar to this:

 

 CELL

A1 B1 C1

 

So A1 would hold my trailer number ex. Trailer (123456)

B1 would hold the percentage ex. 100SL

C1 calculates all SL trailers in the cell for a sum total at end of each shift of shipped trailers

 

so currently I’m using (COUNTIF B1 = 100SL) again this is a simple upgrade from the hand and paper version I initially learned yet seeing how a trailer can be shipped (SL) at ANY percent I had to also continue the countif function as (COUNTIF B1 = 100SL + COUNTIF B1 = 90SL + COUNTIF B1 = 80SL and so on) this is ongoing for the entire 30+ cells I have populated to work with this function.

 

What I want to know is there a function than is more simplistic to this that I can use base off of just the signal of an expression: so for example if a trailer is sealed at 80SL (or whatever percent) how can I have excel continue to count anything ending with “SL” after my numerical value is placed instead of having to continue using a string of nested calculation for different values.

 

5 Replies

@Optimistic92 

 

Is it possible for you to post a sample of your actual spreadsheet, or at the very least a simple mockup that replicates the quandary you're facing? (Not an image, an actual spreadsheet)

 

In the absence of that, or if you would prefer to work just from suggestions--which is fine--then let me suggest the following possibility:

  • Place the SL (or any other codes) in a separate column from the percentage figures that you are counting and summing.
  • You can then use SUM or SUMIF or COUNT or COUNTIF to sum or count the numeric values based on criteria referencing the separate column's codes.

If I were in your place I'd also consider getting rid of the five separate sheets, putting it all into a single database, using a single column to reflect the batch. And yes, another for date, so that a year's worth (or more) can all be in a single table. You'll be even more impressive to your bosses with how you can tabulate comparisons:  quarter to quarter, month to month, week to week. Have you explored the Pivot Table feature? You'll love it.

@mathetes 

I really haven't had much training with the pivot tables yet although I'm in MIS graduate that's nothing I delved into yet. However, I would love to know but I've attached a copy of one of my completed shift excel documents to give you an idea of what I was speaking about. Everything within the document from tab 121, 122, 123, 124, and 125 are also calculated on the last EOS tab (which is end of shift report for data keeping) 

best response confirmed by allyreckerman (Microsoft)
Solution

@Optimistic92 

 

Oh, my, Jeremy. You've done a masterful job of taking what was a paper process and converting it to Excel. The problem is that you've done just that: taken what was a paper process and converted it to Excel.

 

You have done that--and I've seen this happen in other instances too--by imposing that paper process mindset onto Excel, rather than by re-conceptualizing the whole process as a database which can take advantage of Excel's abilities to report on transactions such as movements of trailers.

 

You've done a really good job, and your formulas that count dispatched trailers are impressive examples of the COUNTIFS function. And how you've incorporated the Page Footer into some of the tabs so as to note such anomalies as  "Must Ship" is impressive. But also depressing, in that you literally are picturing this as a page of a report, with a footer that contains--in the case of "Must Ship" at any rate, something that should be its own column--an attribute belonging with a store code on the database. 

 

For your immediate problem, the one you posted, I go back to my recommendation that you separate the SL and other codes from the number that represents a percentage. I've done it for you as a demonstration, on tab 124, and shown two examples of how you could create some cleaner summary data with far simpler formulas. As a general principle, it complicates things unnecessarily to combine two different types of data in a single cell, as you've been doing. A percent PLUS a code for disposition makes it hard to do things with either!

mathetes_0-1631219159521.png

 

I've added a couple other suggestions in text boxes.

 

My long term recommendation would be to get yourself a book (there are many: https://smile.amazon.com/s?k=excel+as+database&ref=nb_sb_noss_2 ) on creating and using databases in Excel. You're clearly smart enough to master this, and there's no reason why you need to take a course--many good Excel users are self-taught through reading of books and manuals and "playing" with what they find there in meeting needs at work. That's how I did it, back in 1971 and 1972, before there were PCs and spreadsheets...on an IBM mainframe using a language called APL. Totally self-taught.

I appreciate this; I believe implementing this process will defiantly continue to make my job more efficient. THANK YOU SO MUCH. & yes! I'm an advocate for learning anything new, once I completed my degree and the concentration of Management of Information System (which mainly organizes data) I became fanatical with implementing my learnings in my everyday life, especially at work which has been a huge help. I certainly appreciate your recommendation and I had initially planned on taking a course in EXCEL to become a more strong user however, I believe I apply the good book knowledge first per your recommendation to see how much my brain will absorb.
Thanks Again

@Optimistic92 

To count all occurrences of cells with content ending in "SL" you could use

= COUNTIFS(I3:M101,"*SL")

I don't like direct cell references so I would use Excel Tables to hold input data, so allowing the ranges to extend as you input data.