how to search vertically then count horizontally in excel
 

Hi I'm trying to work out how to FIND an instance of a name on the vertical
axis, then count any iinstances of a particular text on the horizontal. i.e.

Joe bloggs is in cell A1 on sheet 1 then on sheet 2 he is in cell A10.
Sheet 1 shows what days Joe worked and was on holiday, as does sheet 2.
Sheet 3 "the holiday planner" counts each holiday for joe using the formula
countif. which requires me to choose the correct cell range.

Can I use a formula to find the correct row "joe bloggs" then count the
number of holidays that joe had on seperate sheets?

how to search vertically then count horizontally in excel
 

Is this what you want...

Count the H's for Joe:

...........A.....B.....C.....D
1......Sue....H....H........
2......Bob..........H.....H
3......Joe....H....H.....H
4......Tim..........H........

=COUNTIF(INDEX(B1:D4,MATCH("Joe",A1:A4,0),),"H")

--
Biff
Microsoft Excel MVP


"Novawitt" wrote in message
...
Hi I'm trying to work out how to FIND an instance of a name on the
vertical
axis, then count any iinstances of a particular text on the horizontal.
i.e.

Joe bloggs is in cell A1 on sheet 1 then on sheet 2 he is in cell A10.
Sheet 1 shows what days Joe worked and was on holiday, as does sheet 2.
Sheet 3 "the holiday planner" counts each holiday for joe using the
formula
countif. which requires me to choose the correct cell range.

Can I use a formula to find the correct row "joe bloggs" then count the
number of holidays that joe had on seperate sheets?

how to search vertically then count horizontally in excel
 

Thank You Thank You Thank You. This has been doing my nut in. I will apply
it straight away.

"T. Valko" wrote:

Is this what you want...

Count the H's for Joe:

...........A.....B.....C.....D
1......Sue....H....H........
2......Bob..........H.....H
3......Joe....H....H.....H
4......Tim..........H........

=COUNTIF(INDEX(B1:D4,MATCH("Joe",A1:A4,0),),"H")

--
Biff
Microsoft Excel MVP


"Novawitt" wrote in message
...
Hi I'm trying to work out how to FIND an instance of a name on the
vertical
axis, then count any iinstances of a particular text on the horizontal.
i.e.

Joe bloggs is in cell A1 on sheet 1 then on sheet 2 he is in cell A10.
Sheet 1 shows what days Joe worked and was on holiday, as does sheet 2.
Sheet 3 "the holiday planner" counts each holiday for joe using the
formula
countif. which requires me to choose the correct cell range.

Can I use a formula to find the correct row "joe bloggs" then count the
number of holidays that joe had on seperate sheets?