Forum Discussion
Sum product help
Hello,
I have a column of data containing numbers. Some cells contain multiple numbers separated by commas.
Example:
1
2,3,4
11,4,6,8
My goal was to find the frequency that the number one is listed first in each cell.
I used this: =SUMPRODUCT(- - (LEFT(A1:A100)=“1”))
Why does it only work for single digit numbers?
It works for the 1, but for example it doesn’t work for 11.
I have a column of data containing numbers. Some cells contain multiple numbers separated by commas.
Example:
1
2,3,4
11,4,6,8
My goal was to find the frequency that the number one is listed first in each cell.
I used this: =SUMPRODUCT(- - (LEFT(A1:A100)=“1”))
Why does it only work for single digit numbers?
It works for the 1, but for example it doesn’t work for 11.
1 Reply
Jbisca Well, the LEFT function takes the first character if no number of characters is specified. when you do specify a number (e.g. 1 or 2 or 3) it takes either the first, the first two or the first three characters. It can not take the first for one cell and, for instance the first three in another.
If your Excel version supports the SEQUENCE function, perhaps the attached solution offers an acceptable work-around.
