Using Vlookup to count the contents in a cell

[J Morrow]

I need to utilize vlookup as part of an excel formula and then count the entries in the cell of which the entries are separated by a space.

For example, in one worksheet tab, the reference cell is 12345, using vlookup to search a range (say a1:c8), when a match is found in column a, I then want to sum the number of entries separated by a space in the corresponding cell in column c (of which the contents are: 574 625 895) and the desired return result would be 3.

I know how to perform a similar test using an array formula to sum up the entire column as shown below but I am not sure how to tie in the vlookup so that I am only counting a single cell that I am requiring.

{=SUM(IF(LEN(TRIM(M2:M2281))=0,0,LEN(TRIM(M2:M2281))-LEN(SUBSTITUTE(M2:M2281," ",""))+1))}

[mahju]

Hi

If your data range is c3:d5
input is d7 then use this formula to count the numbers

Please Login or Register  to view this content.

See attached also
Regards

[J Morrow]

Thanks, however, I am receiving false positives; when the searched cell is blank, I receive a count of 1 when it should be 0. It would also be great if the formula could be explained to me so that I can understand what is taking place.

[NBVC]

So are you counting the number of numbers in the cell or summing the numbers? If counting....

=IF(VLOOKUP(X1,$A$1:$C$8,3,0)="","",LEN(VLOOKUP(X1,$A$1:$C$8,3,0))-LEN(SUBSTITUTE(VLOOKUP(X1,$A$1:$C$8,3,0)," ",""))+1)

where X1 contains the input to search for..

[mahju]

Hi

Use this

Please Login or Register  to view this content.

Explaination:

First part of condition checks if the returned value is blank, place "0"
if it is not blank then
Count the total length of input value with len function (supose 10 with three spaces)

Please Login or Register  to view this content.

Then use substitute function to replace blanks (" ") with nothing (""). The length will be decreased (10-3=7) and get the length of new string

Please Login or Register  to view this content.

substrect first length from new length 10-7=3

Add one so answer will be 4 (total pieces are always no. of dpaces +1)
Regards

[J Morrow]

I thank both mahju and NBVC for you responses. Both formulas work, I like the results better from the suggested formula from mahju simply that if the searched cell contains no data, the resulted outcome is 0. You both have answered my requirements and I thank you kindly.

[mahju]

You are welcome

Regards