SUMPRODUCT
 

I have a column that contains two sets of information displayed as

173.05 prepaid
49.95
174.03 prepaid
149.95
149.95

I'd like a sumproduct search that counts only the number of times a cell
containg "prepaid" is listed. There are other criteria and columns which is
why I prefer to use sumproduct but am open to anything. Thanks

JerryS

COUNTIF will do what you asked -- count the number of occurrences of
"prepaid".

On Mon, 6 Jun 2005 18:45:21 -0700, JerryS
wrote:

I have a column that contains two sets of information displayed as

173.05 prepaid
49.95
174.03 prepaid
149.95
149.95

I'd like a sumproduct search that counts only the number of times a cell
containg "prepaid" is listed. There are other criteria and columns which is
why I prefer to use sumproduct but am open to anything. Thanks

JerryS

Thanks but I can't get it to work because the dollars and "prepaid" are in
the same cell. Wow would I write it so that it counts if the cell contains
"prepaid"?
--
JerryS


"Myrna Larson" wrote:

COUNTIF will do what you asked -- count the number of occurrences of
"prepaid".

On Mon, 6 Jun 2005 18:45:21 -0700, JerryS
wrote:

I have a column that contains two sets of information displayed as

173.05 prepaid
49.95
174.03 prepaid
149.95
149.95

I'd like a sumproduct search that counts only the number of times a cell
containg "prepaid" is listed. There are other criteria and columns which is
why I prefer to use sumproduct but am open to anything. Thanks

JerryS

=sumproduct(--(right(a1:a50,7)="prepaid"))

You might also have to use

=sumproduct(--(right(trim(a1:a50),7)="prepaid"))

if there are trailing spaces after 'prepaid'



"JerryS" wrote:

Thanks but I can't get it to work because the dollars and "prepaid" are in
the same cell. Wow would I write it so that it counts if the cell contains
"prepaid"?
--
JerryS


"Myrna Larson" wrote:

COUNTIF will do what you asked -- count the number of occurrences of
"prepaid".

On Mon, 6 Jun 2005 18:45:21 -0700, JerryS
wrote:

I have a column that contains two sets of information displayed as

173.05 prepaid
49.95
174.03 prepaid
149.95
149.95

I'd like a sumproduct search that counts only the number of times a cell
containg "prepaid" is listed. There are other criteria and columns which is
why I prefer to use sumproduct but am open to anything. Thanks

JerryS

you can also use

=COUNTIF(A1:A50,"=*prepaid*")

"JerryS" wrote:

Thanks but I can't get it to work because the dollars and "prepaid" are in
the same cell. Wow would I write it so that it counts if the cell contains
"prepaid"?
--
JerryS


"Myrna Larson" wrote:

COUNTIF will do what you asked -- count the number of occurrences of
"prepaid".

On Mon, 6 Jun 2005 18:45:21 -0700, JerryS
wrote:

I have a column that contains two sets of information displayed as

173.05 prepaid
49.95
174.03 prepaid
149.95
149.95

I'd like a sumproduct search that counts only the number of times a cell
containg "prepaid" is listed. There are other criteria and columns which is
why I prefer to use sumproduct but am open to anything. Thanks

JerryS