receive one value using two or more criteria

[billdad]

Using a spreadsheet with 12 columns and 500 rows that lists staff names,
positions, work locations, and adjacent columns of data related to each name
on the list. First three column headers are Position (10 different positions
listed), Location (9 different locations listed) and Name (500 names, ie:
Smith, Joe). I want to produce the number of staff in a specific position at
a specifc location. For example, the result I want will identify how many
Directors are at the "North" Location, how many at the "South" location, etc.

What formula will work? I haven't been able to get vlookup, or any "count"
formula to work. The directive I have received is to produce a report
itemizing the numbers of positions across the 9 locations for 500+ staff.
Help.

Thanks.

 

[Dave]

Hi,
Perhaps something like:
=SUMPRODUCT(--(A2:A500="Director")*(B2:B500="North")*(ISTEXT(C2:C500)))
Regards - Dave.

 

[Dave]

Or just:
=SUMPRODUCT(--(A2:A500="Director")*(B2:B500="North"))
Dave.