Tuesday, January 2, 2018

Excel: Quick way to check if a cell begins with a value

For this exercise, I wanted to make a pivot table to help me summarize my orders by product type.  I had a single column with the Product Name, but sometimes a product is included in a bundle so I wanted to create a column for each product and put a 1 or a 0 in the column if that item was included in that sale bundle.  

Let's same the product names are:
Creative Lettering Journal
The Guide to Mindful Lettering
Ultimate Lettering Bundle (This has both the Lettering Journal and the Guide)

Since the first three letters of each product are distinct I can use those to differentiate each.  Assume the Product Name is in Column A.  In the column where I'm tallying the Creative Lettering Journals I use the following formula:

formula =IF(Left(A2,3)="Cre",1,if(Left(A2,3)="Ult",1,0))

Now I have a single column that tells me exactly how many Creative Lettering Journals were sold.

No comments:

Post a Comment