COUNTIFS or SUMPRODUCT with multiple criteria including comparing value to another column

[Waterb0y]

I have been searching for a solution to this problem for quite a while and any help is appreciated. I'm not even sure that this is possible with a formula.

I have a sheet of student information of all students tracked by ID number. I can use a COUNTIF to return a count of students based on the value of any of the other fields (ie. full / part time, male or female, etc) then I need to check that list against another list and return a number of matches. So, for example, count number of Full Time ("F") applicants who were accepted ("Accepted") from one list, then compare that list to students who enrolled located in another table.

Applied
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Gender[/TD]
[TD]F/P Time[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]M[/TD]
[TD]F[/TD]
[TD]Accepted[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]Accepted[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]M[/TD]
[TD]F[/TD]
[TD]Accepted[/TD]
[/TR]
</tbody>[/TABLE]

I would want to count all full time who were accepted so I would get ID no's (1,2,3) and then compare the list to the ID column on the table below:

Enrolled
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Class[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]FR[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Joan[/TD]
[TD]FR[/TD]
[TD]NY[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Brad[/TD]
[TD]SR[/TD]
[TD]NY[/TD]
[/TR]
</tbody>[/TABLE]

I would then see that number 1 and 2 enrolled, but 3 did not so my count would be "2". 2 students from the list of applicants who were accepted actually enrolled.


I can't seem to just use a COUNTIF(Applied[F/P Time],"F",Applied[Status],"Accepted",Applied[ID],Enrolled[ID]), I get N/A or 0. I also can't seem to get a SUMPRODUCT formula to work either. Would I need to return an array to compare to the Enrolled[ID] column?

I am trying to accomplish this without having to add columns to worksheets, but I am willing to use a hidden sheet to perform calculations if needed.

 

[Joe4]

Welcome to the Board!

I can't seem to just use a COUNTIF(Applied[F/P Time],"F",Applied[Status],"Accepted",Applied[ID],Enrolled[ID]), I get N/A or 0.

COUNTIF only allows for one condition, but COUNTIFS allow for multiple conditions (COUNTFS was introduced in Excel 2007).
See: https://www.techonthenet.com/excel/formulas/countifs.php

 

[Waterb0y]

Sorry, that was a misprint. I meant COUNTIFS(Applied[F/P Time],"F",Applied[Status],"Accepted",Applied[ID],Enrolled[ID]).

 

[Joe4]

One way would be to add a calculated field to the Applied table doing a COUNTIF to count the number of times that particular ID exists in the Enrolled table.
Then you last condition could just check to see if that Calculated amount is greater than 0.

 

[Waterb0y]

I am considering doing the calculated column, but before I do I'm trying to figure out if there is a way to accomplish the same thing without one. Honestly, I don't even know if it is possible.

There are times when I need to count a subset of students filtered from one list and tracked through multiple years and a way to do this with a formula would save the complication of adding new columns to worksheets.

Thank you for the help thus far.

 

[Joe4]

What you are really describing is a relational database. All things equal, I prefer to use a database program (like Access or SQL for such tasks).
You can often use brute-force methods in Excel to get the answers, but I find it so much easier using a program like Access, that was designed for such purposes (though it helps I am quite comfortable using Access, and have the ability to use it).

There are some formula gurus on this board who can do amazing things. Perhaps one will see this and be able to come up with a formula. I am thinking it might be some type of array formula.

 

[Waterb0y]

bump