Combining COUNTIFS with a VLOOKUP

0
votes

I have looked through other posts on here and cannot find the solution I need.

I am currently using this formula:

=COUNTIFS(Tracking!$F$3:$XA$3,$F1,Tracking!$F4:$XA4,"~*")

This is counting all the * values that match to the reference "Attainment" (This is cell F1), but is relying on my 2 worksheets to have the people listed in exactly the same order. What I really want is the countif to calculate based on matching the person ID.

I have 2 sheets that use a unique ID for each person. I want to be able to match this ID and then run the countif, so in essence combine a VLOOKUP with the COUNTIFS. I did try adding another criteraia to the COUNTIFS but this doesn't seem to work. No IDs are repeated in either sheet. The unique ID defines each row and the row holds a very large amount of grade data for the person.

Sample Data This is my grading data

This is my summary sheet with COUNTIFS

1
excelexcel-formulavlookupcountif
Can you please edit your question to give us sample data and expected outcome? – cybernetic.nomad
Pictures of data now added @cybernetic.nomad – MIS_Gem

1 Answers

0
votes

I think you could accomplish this using this formula in F3

=COUNTIF(INDIRECT("Tracking!"&MATCH(A3,Tracking!$A:$A,0)&":"&MATCH(A3,Tracking!$A:$A,0)),"*~*")

This is using the row number of the match to COUNTIFS any cells with a * in that row.