SUMPRODUCT

[Guest]

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

 

[Myrna Larson]

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

 

[Guest]

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"?

 

[Guest]

=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'

 

[Guest]

you can also use

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

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"?