how to search vertically then count horizontally in excel

N

Novawitt

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

T. Valko

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")
 
N

Novawitt

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top