dear all,
i have a table as below :
col A col B col C col D col E
John S S V V
Mary V S S V
Pete V V S V
Lucy V S S S

i need to have another sheet summary to count by name their total
number of S which suppose the result should be :

John 2
Mary 2
Pete 1
Lucy 3

I suppose to use countif, but cannot figure out the formula, need
yours help.

Thanks in advance.
Patrick.

hi,

if the data is on Sheet1 and the summary is on Sheet2

=COUNTIF(INDIRECT("Sheet1!"&ADDRESS(MATCH(A1,Sheet1!A:A,0),2)&":"&ADDRESS(MATCH(A1,Sheet1!A:A,0),5)),"S")

--
isabelle


Le 2011-12-06 03:03, PatLee a écrit :
> dear all,
> i have a table as below :
> col A col B col C col D col E
> John S S V V
> Mary V S S V
> Pete V V S V
> Lucy V S S S
>
> i need to have another sheet summary to count by name their total
> number of S which suppose the result should be :
>
> John 2
> Mary 2
> Pete 1
> Lucy 3
>
> I suppose to use countif, but cannot figure out the formula, need
> yours help.
>
> Thanks in advance.
> Patrick.

or this one, a little less long,

=COUNTIF(INDIRECT("Sheet1!"&MATCH(A1,Sheet1!A:A,0)&":"&MATCH(A1,Sheet1!A:A,0)),"S")

--
isabelle

You could use col F of sheet1 to hold the totals, e.g. put this
formula in F1:

=COUNTIF(B1:E1,"S")

and copy down. Then in your summary sheet you can just link to F1 if
the names are in the same order on both sheets:

=Sheet1!F1

and copy down. If the names may be in a different order, then on the
summary sheet you can have this:

=INDEX(Sheet1!F:F,MATCH(A1,Sheet1!A:A,0))

and copy this down, assuming the first name is in cell A1 of the
summary sheet.

Hope this helps.

Pete

On Dec 6, 8:03*am, PatLee <patlee...@gmail.com> wrote:
> dear all,
> i have a table as below :
> col A col B col C col D col E
> John *S * * *S * * * V * * * V
> Mary *V * * *S * * * S * * * V
> Pete * V * * *V * * * S * * * V
> Lucy * V * * *S * * * S * * * S
>
> i need to have another sheet summary to count by name their total
> number of S which suppose the result should be :
>
> John *2
> Mary *2
> Pete *1
> Lucy *3
>
> I suppose to use countif, but cannot figure out the formula, need
> yours help.
>
> Thanks in advance.
> Patrick.

On Dec 7, 9:15*am, Pete_UK <pete.ashu...@yahoo.com> wrote:
> You could use col F of sheet1 to hold the totals, e.g. put this
> formula in F1:
>
> =COUNTIF(B1:E1,"S")
>
> and copy down. Then in your summary sheet you can just link to F1 if
> the names are in the same order on both sheets:
>
> =Sheet1!F1
>
> and copy down. If the names may be in a different order, then on the
> summary sheet you can have this:
>
> =INDEX(Sheet1!F:F,MATCH(A1,Sheet1!A:A,0))
>
> and copy this down, assuming the first name is in cell A1 of the
> summary sheet.
>
> Hope this helps.
>
> Pete
>
> On Dec 6, 8:03*am, PatLee <patlee...@gmail.com> wrote:
>
>
>
> > dear all,
> > i have a table as below :
> > col A col B col C col D col E
> > John *S * * *S * * * V * * * V
> > Mary *V * * *S * * * S * * * V
> > Pete * V * * *V * * * S * * * V
> > Lucy * V * * *S * * * S * * * S
>
> > i need to have another sheet summary to count by name their total
> > number of S which suppose the result should be :
>
> > John *2
> > Mary *2
> > Pete *1
> > Lucy *3
>
> > I suppose to use countif, but cannot figure out the formula, need
> > yours help.
>
> > Thanks in advance.
> > Patrick.

dear isabelle, pete,
thanks for yours help, will try it.

isabelle,
i'm not good at excel function syntax, would you please explain more
in detail those "" and & in your formula? when I copied it into my
excel, it showed "#NAME?"

Patrick.

hi Patrick,

did you copy the names on Sheet2 in the range A1: A4
and then put the formula in range B1 of Sheet2 ?

=COUNTIF(INDIRECT("Sheet1!"&MATCH(A1,Sheet1!A:A,0)&":"&MATCH(A1,Sheet1!A:A,0)),"S")

one could write the formula like this:
=COUNTIF(Sheet1!1:1,"S")

i used the INDIRECT formula to replace this part 1:1
like for write :

INDIRECT("Sheet1!"&1&":"&1)

and after i used the formula MATCH to find the line number

INDIRECT("Sheet1!"&MATCH(A1,Sheet1!A:A,0)&":"&MATCH(A1,Sheet1!A:A,0))

--
isabelle



Le 2011-12-07 02:43, PatLee a écrit :

> i'm not good at excel function syntax, would you please explain more
> in detail those "" and& in your formula? when I copied it into my
> excel, it showed "#NAME?"
>
> Patrick.

On Dec 7, 9:24*pm, isabelle <i...@v.org> wrote:
> hi Patrick,
>
> did you copy the names on Sheet2 in the range A1: A4
> and then put the formula in range B1 of Sheet2 ?
>
> =COUNTIF(INDIRECT("Sheet1!"&MATCH(A1,Sheet1!A:A,0)&":"&MATCH(A1,Sheet1!A:A,*0)),"S")
>
> one could write the formula like this:
> * =COUNTIF(Sheet1!1:1,"S")
>
> i used the INDIRECT formula to replace this part 1:1
> like for write :
>
> INDIRECT("Sheet1!"&1&":"&1)
>
> and after i used the formula MATCH to find the line number
>
> INDIRECT("Sheet1!"&MATCH(A1,Sheet1!A:A,0)&":"&MATCH(A1,Sheet1!A:A,0))
>
> --
> isabelle
>
> Le 2011-12-07 02:43, PatLee a crit :
>
>
>
> > i'm not good at excel function syntax, would you please explain more
> > in detail those "" and& *in your formula? when I copied it into my
> > excel, it showed "#NAME?"
>
> > Patrick.

Isabelle,
got it, many thanks.