Formula to calculate number of unique names in column H?

[Mr.Castle]

I want to calculate the total number of cells with the same name from H6:H50, every cell repeated with the same name would be counted as 1, such as :

H6=APPLE
H7= APPLE
H8=APPLE
H9= BANANA
H10=APPLE
H11= BANANA
H12=MANGO

APPLE+BANANA+MANGO=3

Every name would only be counted as 1, the formula would identify the name as data, so that space or uppercase differences wont affect the result.

[6StringJazzer]

Try this one:

Please Login or Register  to view this content.

[Mr.Castle]

Hi , The formula worked only with cells that contains data, with blank cells the result is #N/A, its possible to ignore blank cells?

[FlameRetired]

Hi Mr.Castle.

Jeff is apparently offline at the moment. With this added criteria comes a nested IF function which means the formula now has to be array entered.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[Mr.Castle]

The modified formula worked perfectly,

Thank you and Jeff

[FlameRetired]

You are welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.