SUMPRODUCT(--(LEFT($G$2:$G$150,10)="employment"))
In column G are groups of economic data. In column B are the numbers that
correspond to that group. The problem is that not all of the corresponding
€œemployment€� cells in column B will have numbers. I need to be able to come
up with only the number of cells in column B that contain numbers.
SUMPRODUCT(--(LEFT($G$2:$G$150,10)="employment")) returns the number 19 for
the 19 times €œemployment€� is in column G.
The cells in column B that are directly to the left of the €œemployment€�
cells at this time contain 14 numbers. I need a formula or modify this
formula to be able to return the value of 14.
Thanks for the help

mmcap,

Try

=SUMPRODUCT((LEFT($G$2:$G$150,10)="employment")*IS NUMBER(B2:B150))

HTH,
Bernie
MS Excel MVP


"mmcap" wrote in message
...
SUMPRODUCT(--(LEFT($G$2:$G$150,10)="employment"))
In column G are groups of economic data. In column B are the numbers that
correspond to that group. The problem is that not all of the corresponding
"employment" cells in column B will have numbers. I need to be able to come
up with only the number of cells in column B that contain numbers.
SUMPRODUCT(--(LEFT($G$2:$G$150,10)="employment")) returns the number 19 for
the 19 times "employment" is in column G.
The cells in column B that are directly to the left of the "employment"
cells at this time contain 14 numbers. I need a formula or modify this
formula to be able to return the value of 14.
Thanks for the help

works great I just had to add the $ for the range in order for it to work in
both of the ways I sort the data.
Thanks much
Norm

"Bernie Deitrick" wrote:

mmcap,

Try

=SUMPRODUCT((LEFT($G$2:$G$150,10)="employment")*IS NUMBER(B2:B150))

HTH,
Bernie
MS Excel MVP


"mmcap" wrote in message
...
SUMPRODUCT(--(LEFT($G$2:$G$150,10)="employment"))
In column G are groups of economic data. In column B are the numbers that
correspond to that group. The problem is that not all of the corresponding
"employment" cells in column B will have numbers. I need to be able to come
up with only the number of cells in column B that contain numbers.
SUMPRODUCT(--(LEFT($G$2:$G$150,10)="employment")) returns the number 19 for
the 19 times "employment" is in column G.
The cells in column B that are directly to the left of the "employment"
cells at this time contain 14 numbers. I need a formula or modify this
formula to be able to return the value of 14.
Thanks for the help