countif

[PatLee]

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.

 

[isabelle]

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]

or this one, a little less long,

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

 

[Pete_UK]

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

 

[PatLee]

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

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.

 

[isabelle]

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 :

 

[PatLee]

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,
got it, many thanks.