CODEWITHSUNDEEP
excelnestedvlookupcountif
wh1016
wh1016

Reputation: 21

How to correctly nest a VLOOKUP and COUNTIF function in Excel?

So in column B(b2:b1613) I have a list of peoples initials. In column E(e2:e1613) I have the box # they used that day (from 1-14).

I have a separate table that i want to be able to calculate how many times a specific person (column b) uses a specific box number. So my rows are the initial options and the columns are the box numbers 1-14.

So, basically the function needs to say if the value in column b is equal to the person's initials (it'll reference column q2 and so on) and the box number is the same as the column it's currently in (r through ae) then count it.

I have made A LOT of attempts. This is one I have so far that hasn't worked. Sorry if my attempt was completely horrid - I have researched online and can't seem to find a situation that is similar to mine. 

=VLOOKUP([@Anest],B2:E1613,COUNTIF(E2:E1613,Table4[[#Headers],[1]]))

Anyways, any help would be greatly appreciated.

Upvotes: 1

Views: 8016

Answers (1)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

You shouldn't need VLOOKUP - a simple COUNTIFS should suffice:

=COUNTIFS($B$2:$B$21,[@Anest],$E$2:$E$21,Table1[[#Headers],[1]])

Upvotes: 1

Related Questions