Counting across ranges across multiple sheets

[JDC8457]

I have a formula that works well for counting one value (K3) across a range (F4:F16) within multiple sheets.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$3:$A$6&"'!f4:f16"),k3))

I'm counting the number of students (in this example, 2) who have applied to a given college (in this example, the University of MN). However, I'd like to modify my formula so that it finds the college but also counts the 5 different Application Status's for the students applying at the college: Accepted, Enrolled, Incomplete, Denied, Applied. The total number of applicants should be equal to the number of different application status'.

Query By College App Status Count
College Name --> University of Minnesota - Twin Cities ACC 2
DND
ENR
INC
APP

Thanks in advance.

 

[Chihiro]

Upload sample workbook with your current set up please. Along with some manually inputted expected output.

 

[JDC8457]

Thanks.

Stand by...trying to shrink the file size.

 

[JDC8457]

I would expect to see a value of 1 in cell G3 and G7 on the Totals tab.

My formula works fine for counting the number of colleges on the tabs, but I cannot determine how to count the different app status for each college.

thanks!

 

[John Jairo V]

Hi, to all!

You could try in G3:
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&A$3:A$4&"'!B4:B15"),LOOKUP("zzz",E$3:E3),INDIRECT("'"&A$3:A$4&"'!H4:H15"),F3))

And drag it down. Blessings!

 

[JDC8457]

You guys are the best!!! I"ll give it a try.