COUNTIFS Formula Not Working

[emirahuhu]

Hi, so I have an excel sheet here that needs the CountIFS Formula. I am not sure why it isn't working and there doesn't seem to be anything wrong with the formula but it keeps returning zero.

I have tried removing the dropdown list and the data validation but it is still returning zero. Any advice?

The formula that I have used is this:

=COUNTIFS(ALL!$A$1:$H$1355,'SUMMARY REPORT 2023'!A6,ALL!$A$1:$H$1355,'SUMMARY REPORT 2023'!G2)

Please note that on the criteria range there are like 300+ rows that are hidden. I am not sure if that has any effect with this formula.

This can be found on the Summary Report tab on F6. I am attaching here the sheet as well. Any help would be wonderful.

Sample Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1	2023	LIFETIME COUNT					SCHOLAR STATUS as of current SY
2		LIFETIME SCHOLARS	GRADUATES	FALLOUT	BREACH	Beg'g 2022	ACTIVE	GRADUATED	FALLOUT	BREACH
3	TOTAL
4	TEACHSTEM					258	108
5	TeachSTEM College					150	0	0	0	0
6	TeachSTEM College Private					0
7	TeachSTEM College Public					0
8	TeachSTEM Masters					108	108	0	0	0
9	TeachSTEM Masters Private
10	TeachSTEM Masters Public
SUMMARY REPORT 2023

Cell Formulas
Range	Formula
F4:G4	F4	=SUM(F5:F8)
G5,G8	G5	=COUNTIFS(ALL!$G$3:$G$5001,'SUMMARY REPORT 2023'!A5,ALL!$D$3:$D$5001,'SUMMARY REPORT 2023'!$G$2)
H8:J8,H5:J5	H5	=COUNTIFS(ALL!$G$3:$G$5001,'SUMMARY REPORT 2023'!$A5,ALL!$D$3:$D$5001,'SUMMARY REPORT 2023'!H$2,ALL!$E$3:$E$5001,$A$1)
F6:F7	F6	=COUNTIFS(ALL!$A$1:$H$1355,'SUMMARY REPORT 2023'!A6,ALL!$A$1:$H$1355,'SUMMARY REPORT 2023'!G2)

Named Ranges
Name	Refers To	Cells
ALL!_FilterDatabase	=ALL!$A$2:$H$1355	F6:F7

Sample Sheet.xlsx
	A	B	C	D	E	F	G	H
574	tsc157	Ballejo	Princess Ella	Active			TeachSTEM College Public	N/A
575	tsc158	Banagua	Mary Grace	Active			TeachSTEM College Public	N/A
576	tsc159	Borlagdan	Queenie	Active			TeachSTEM College Public	N/A
577	tsc160	Cadut	Christel Joy	Active			TeachSTEM College Public	N/A
578	tsc161	Cuberos	Mechaellah Joy	Active			TeachSTEM College Public	N/A
579	tsc162	Gemperoso	Angela	Active			TeachSTEM College Public	N/A
580	tsc163	Gernale	James Ian	Active			TeachSTEM College Public	N/A
581	tsc164	Jacalan	Arren Jean	Active			TeachSTEM College Public	N/A
582	tsc165	Larra	Jhon Dave	Active			TeachSTEM College Public	N/A
583	tsc166	Lazarte	Jedwin James	Active			TeachSTEM College Private	N/A
584	tsc167	Llamando	John Edzel	Active			TeachSTEM College Public	N/A
585	tsc168	Lozada	Jaren Paul	Active			TeachSTEM College Private	N/A
586	tsc169	Mabunay	Honeylyn	Active			TeachSTEM College Public	N/A
587	tsc170	Madronio	Ma. Jemenica Angela	Active			TeachSTEM College Public	N/A
588	tsc171	Nieva	Maria Connie	Active			TeachSTEM College Public	N/A
589	tsc172	Nuez	Jasmine	Active			TeachSTEM College Public	N/A
590	tsc173	Oblefias	Christian	Active			TeachSTEM College Public	N/A
591	tsc174	Palaña	Hannah Mae	Active			TeachSTEM College Public	N/A
592	tsc175	Patingga	Francis Aillaine	Active			TeachSTEM College Public	N/A
593	tsc176	Saga	Manuel	Active			TeachSTEM College Public	N/A
594	tsc177	Singson	Jay Ann	Active			TeachSTEM College Public	N/A
595	tsc178	Socobos	April Lee	Active			TeachSTEM College Public	N/A
596	tsc179	Tagapulot	Xyreece Angel	Active			TeachSTEM College Public	N/A
597	tsc180	Vidad	Joanna	Active			TeachSTEM College Public	N/A
598	tsc181	Villaflores	Giselle	Active			TeachSTEM College Private	N/A
ALL

Cells with Data Validation
Cell	Allow	Criteria
G574:G696	List	Endowment,. LTG, NextGen, St. Stephen, STEM GG-BU, TeachSTEM College Public, TeachSTEM College Private, TeachSTEM Master Public, TeachSTEM Master Private,YSA
D574:D598	List	Active, Fallout, Breach, Graduated, To Graduate

 

[awoohaw]

Your sample only has two unique values in Column G. but, its enough to tinker with.

 

[Peter_SSs]

Looks like it should be like this to me.

BTW, it is a bad idea to use the worksheet name that the formula is on, in the formula itself (SUMMARY REPORT 2023)
It can lead to errors in some instances.

emirahuhu.xlsm
	A	B	C	D	E	F
1	2023	LIFETIME COUNT
2		LIFETIME SCHOLARS	GRADUATES	FALLOUT	BREACH	Beg'g 2022
3	TOTAL
4	TEACHSTEM					283
5	TeachSTEM College					150
6	TeachSTEM College Private					3
7	TeachSTEM College Public					22
SUMMARY REPORT 2023

Cell Formulas
Range	Formula
F4	F4	=SUM(F5:F8)
F6:F7	F6	=COUNTIFS(ALL!$G$1:$G$1355,A6,ALL!$D$1:$D$1355,G$2)

 

[awoohaw]

Also, Column ALL tab is also blank. It seems it should be a year number or a date. But unsure of which.

 

[emirahuhu]

Looks like it should be like this to me.

BTW, it is a bad idea to use the worksheet name that the formula is on, in the formula itself (SUMMARY REPORT 2023)
It can lead to errors in some instances.

emirahuhu.xlsm
	A	B	C	D	E	F
1	2023	LIFETIME COUNT
2		LIFETIME SCHOLARS	GRADUATES	FALLOUT	BREACH	Beg'g 2022
3	TOTAL
4	TEACHSTEM					283
5	TeachSTEM College					150
6	TeachSTEM College Private					3
7	TeachSTEM College Public					22
SUMMARY REPORT 2023

Cell Formulas
Range	Formula
F4	F4	=SUM(F5:F8)
F6:F7	F6	=COUNTIFS(ALL!$G$1:$G$1355,A6,ALL!$D$1:$D$1355,G$2)

Thank you so much for this, advice! This totally worked!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.