Is Countifs right you use?

[TSing3]

I’m trying to return the number of times a name appears in a range of cells based on the criteria of another cell. In column AT I have names of departments: Safety, Quality, FI, etc. In columns AX:BG I have a bunch of people’s names.

On another sheet I’m trying to find how many times a name, Jack for example, appears in the table in the same row that Safety appears in AT. I’m basically trying to figure out how many safety projects each person is involved in.

 

[TSing3]

The second one worked after I entered with ctrl+shift+enter but when I drag it says Excel ran out of resources while attempting to calculate one or more formulas.

 

[Fluff]

How many rows & columns are you trying to drag it?

 

[TSing3]

Probably between 40-50

 

[Fluff]

I wouldn't have though that would cause any problems, do you have loads of other formulae on the sheet?

 

[Eric W]

You can try amending Dante's formula to:

=COUNTIF(INDEX(Sheet1!$AX$2:$BG$4,MATCH(B$1,Sheet1!$AT$2:$AT$4,0),0),$A2)

which is a non-array formula, which might help the resource issue.