Excel if Formula

Old Perry

Hi can somebody advise please or provide a link to some instructions, thanks.

I would like to add a formula to a column where by if a given cells (product) code begins with 1 or 2 a corresponding cell will show a certain text depending on which.

i would also like to account for times where by the formulated column may be blank or contain a different value and so the corresponding cell should then remain to be manually filled.

JoyPad

Have a look here: https://support.office.com/en-ie/article/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

For the first scenario, you probably need LEFT to extract the start of the product code.

e.g. IF( LEFT(A2, 1) = "1", "One", "Not One" )
If the first character in the A2 cell is "1", then value of this cell will be "One", otherwise it will be "Not One".

To check if a cell is empty, use ISBLANK.

smuggler.ie

This should cover other values in the cells, paste in top cell and drag it down.
in B1: =IF(LEFT(A1,1)="1","Text1",(IF(LEFT(A1,1)="2","Text2","other or empty")))

return count of code "1????" in D13: =COUNTIF(B1:B10,"Text1")

return count of code "2????" in D14: =COUNTIF(B1:B10,"Text2")

return count of code "any other value" in D15: =COUNTIF(B1:B10,"other or empty")

Old Perry

Thanks for the above. I will experiment and be back with more questions surely.

smuggler.ie

Or, instead counting "text" values, you could count actual codes starting with "1", etc.


=SUMPRODUCT(--(LEFT($A$1:$A$10,1)="1"))