SUMPRODUCT

[JerryS]

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

On Mon, 6 Jun 2005 18:45:21 -0700, JerryS <[email protected]>
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

[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 <[email protected]>
> 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
>
>

[Duke Carey]

=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 <[email protected]>
> > 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
> >
> >

[Duke Carey]

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 <[email protected]>
> > 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
> >
> >