COUNTIF and VLOOKUP

I am working on attendance calendars. But it is possible that Employees/ID's are on different rows in the monthly calendars.
Is there a way to do COUNTIF and VLOOKUP when the data isn't always on the same row?
Much appreciated.

Look at COUNTIFS where the first criteria (for you) is your ID.

The postedfile references another file [Copy of PCR Attendance 2016.xlsx] which is not available,

As you are not using column A on the Summary sheet, you can put this formula in cell A4:

=IFERROR(INDEX(Employee!C:C,MATCH(C4,Employee!D:D,0)),"")

then copy down - it returns the employee number when it recognises a name in column C (Hint: change the names in C11 and C18 to ones that are in your table of employees).

Then you can put this formula in cell E4:

=IFERROR(COUNTIF(INDEX(INDIRECT("'"&E$2&"'!E11:AI43"),MATCH(VLOOKUP(LOOKUP(99999,$A$4:$A4),Employee!$C:$D,2,0),INDIRECT("'"&E$2&"'!B11:B43"),0),),$D4),"")

and this can be copied across and down your table as far as you need it. The attached file demonstrates this (I've added some made-up data to the JAN and FEB sheets to test it out).

Hope this helps.

Pete

Pete, you are a freaking genius! Once again you've made sense of this muddle brain of mine and pulled it out for me. I was just about to apologize to JohnTopley. I really do suck at clarity and concise explanations here and history has shown that for as hard as I try there is always at least one stupid oversight in everything I post. Pretty sure it must drive some of y'all here crazy. But Pete - moments like this when I'm understood even through the muddle, it gives me a little boost and I hear myself saying, "Rock on, Brenda." And I keep plugging. And the "Pete moments" are such an awesome gift for the muddle and morale. Rock on, Pete. And thanks again!

You're welcome, Brenda - thanks for the kind words.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Pete. I had it. Understood it. Made a few changes. Broke it. Would you mind taking a look at your formula if you have a minute. Pls. https://www.excelforum.com/excel-gen...d-vlookup.html

lol
Thank you.
Thought once a thread was marked as "Solved" you couldn't go back. Which is why I started a new thread. Maybe I missed something? I'll check closer next time I break something. I will repost. Thanks again.

Meant repost as in continue not start a new thread. Thanks.

I had a functioning COUNTIF/VLOOKUP (thanks to Pete_UK but I made a few adjustments and broke it. Apologies to Pete_UK.
The original code totalled up Leave Codes for employees, regardless of the calendar month or their position within the calendar on the Summary Sheet.
Only Jan and Feb are included with the WS. The rest of the months will be added later.

You don't really need that ID2 now in column A, as it is the same as column B.

There are two problems with the formula - your ID numbers are now 6 or more digits (they were only 3-digits before), and so the 99999 needs to be increased, and the lookup should start with $A$3:$A3. Here's a revised formula for E3 in the Summary sheet, with changes shown in red:

=IFERROR(COUNTIF(INDEX(INDIRECT("'"&E$2&"'!E11:AI134"),MATCH(VLOOKUP(LOOKUP(999999999,$A$3:$A3),Data_Validation_List!$C:$D,2,0),INDIRECT("'"&E$2&"'!B11:B134"),0),),$D3),"")

The 999999999 can be any number larger than what you expect for your IDs, eg. 1E100

Hope this helps.

Pete

Try

in E3

=IFERROR(COUNTIF(INDEX(INDIRECT("'"&E$2&"'!E11:AI134"),MATCH(VLOOKUP(LOOKUP(999999,$B$3:$B3),Data_Validation_List!$C:$D,2,0),INDIRECT("'"&E$2&"'!B11:B134"),0),),$D3),"")

Column A now appears redundant.

Aw... Wondered what the 99999's were for. Would never have thought about increasing them. And I figured the same thing about Col A but wasn't gonna tackle that until I figured out the primary screw-up. Mission accomplished. Thank you once again Pete. Sorry for the prob.

Brenda

Exactly! Col. A gone and the reminder to change the range to B was appreciated. And the 999999? Who knew? Me, now lol Answers and lessons from my muddle makes Brenda a Happy Camper once again. And off... Thanks as well JohnTopley.

Originally Posted by BDD2015

...Sorry for the prob...

Well, the problem was for you, not me.

Glad you got it sorted in the end - I think you can now mark this (and your other thread) as SOLVED.

Pete