SumIFs or CountIFs Formula help.

[Mykahlia]

Hello,

I am new to a lot of formula's and excel. I am trying to write a CountIFs or SumIfs formula that will give a total based off specific criteria with in a date range. This is the formula I am trying to use and I keep getting a name error so I am not sure why. Any help is appreciated. If anyone could help with getting the correct formula so we can start pulling the data, I again would be most grateful. Thank you.

=COUNTIFS('Master List'!CA3:CA,"Agent",'Master List'!CB3:CB,"Full S&S",'Master List'!CD3:CD,"AB",'Master List'!CE3:CE,"INS",'Master List'!CF3:CF,"MNL",'Master List'!CC3:CC,">="&DATE(2025,3,31),'2025'!C:C,"<="&DATE(2025,4,6))

This is where the data should be filtering from.

This is where the data needs to read the results from the pulled data above.

 

[etaf]

just looking at the formula ONLY
you dont have a row after the CA or CB etc
so all your ranges need a row range

=COUNTIFS('Master List'!CA3:CA,"Agent",'Master List'!CB3:CB,"Full S&S",'Master List'!CD3:CD,"AB",'Master List'!CE3:CE,"INS",'Master List'!CF3:CF,"MNL",'Master List'!CC3:CC,">="&DATE(2025,3,31),'2025'!C:C,"<="&DATE(2025,4,6))

also you reference a different SHEET
'2025'!C:C," and the full column reference

so something like maybe = change the row 1000 to the maximum rows likely - I also used the same sheet and reference for the between dates

=COUNTIFS('Master List'!CA3:CA1000,"Agent",'Master List'!CB3:CB1000,"Full S&S",'Master List'!CD3:CD1000,"AB",'Master List'!CE3:CE1000,"INS",'Master List'!CF3:CF1000,"MNL",'Master List'!CC3:CC1000,">="&DATE(2025,3,31),'Master List'!CC3:CC1000,"<="&DATE(2025,4,6))

BUT as this is images - difficult to see and multiple sheets
AND a large file - NOT a sample
Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Mykahlia]

CountIFsTestSheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T																																																										BZ	CA	CB	CC	CD	CE	CF
1	Meta							Completed by BPO																																																																							FTE Sheet
2														Qualtrics only
3	Concatenate	SSC Name	Atlas First	Atlas Last	Partner	ACE?	Date Received	Brand	First Name	Last Name	Agent / Class Type	Site	Role	Email address	Qualtrics Y/N?	Base Brand	Class Name	Training Start	Graduation																																																												Role	Class type	Week Beginning	Brand	Partner	Site
4	ArnulfoAbundoJr	Arnulfo Abundo Jr	ARNULFO	ABUNDO JR	SPI	N	3/19/2025	AB	Arnulfo	Abundo Jr	Boot Camp	MNL	Agent	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Agent	Full S&S	3/24/2025	AB	INS	MNL
5	BrisaFernandaAreas	Brisa Fernanda Areas	BRISA FERNANDA	AREAS	SPI	N	3/19/2025	AB	Brisa Fernanda	Areas	Boot Camp	MNL	Agent	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Agent	Full S&S	3/24/2025	AB	INS	MNL
6	CharinaGantalao	Charina Gantalao	CHARINA	GANTALAO	SPI	N	3/19/2025	AB	Charina	Gantalao	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
7	CheslyMartin	Chesly Martin	CHESLY	MARTIN	SPI	N	3/19/2025	AB	Chesly	Martin	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
8	ChristineMaePingkian	Christine Mae Pingkian	CHRISTINE MAE	PINGKIAN	SPI	N	3/19/2025	AB	Christine Mae	Pingkian	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
9	Ck-AnneOlivares	Ck-Anne Olivares	CK-ANNE	OLIVARES	SPI	N	3/19/2025	AB	Ck-Anne	Olivares	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
10	CristinaPriela	Cristina Priela	CRISTINA	PRIELA	SPI	N	3/19/2025	AB	Cristina	Priela	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
11	DietrichRuiz	Dietrich Ruiz	DIETRICH	RUIZ	SPI	N	3/19/2025	AB	Dietrich	Ruiz	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
12	DionGeorgePagulayan	Dion George Pagulayan	DION GEORGE	PAGULAYAN	SPI	N	3/19/2025	AB	Dion George	Pagulayan	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
13	EllaMaeDailo	Ella Mae Dailo	ELLA MAE	DAILO	SPI	N	3/19/2025	AB	Ella Mae	Dailo	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
14	ElviaSinaiEscobar	Elvia Sinai Escobar	ELVIA SINAI	ESCOBAR	SPI	N	3/19/2025	AB	Elvia Sinai	Escobar	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
15	ElvisDowns	Elvis Downs	ELVIS	DOWNS	SPI	N	3/19/2025	AB	Elvis	Downs	Boot Camp	MNL	Trainer	N/A	N	V	INS Boot Camp	3/25/2025	3/27/2025																																																												Trainer	Full S&S	3/24/2025	AB	INS	MNL
Master List

Cell Formulas
Range	Formula
CA4:CA15	CA4	=M4
CB4:CB15	CB4	=IFNA(INDEX('https://1800flowers-my.sharepoint.com/personal/chorne_harryanddavid_com/Documents/[FY25 MDAY Inspiro Credentials Log-Test.xlsb]Class dates lookup'!L:L,MATCH(Q4,'https://1800flowers-my.sharepoint.com/personal/chorne_harryanddavid_com/Documents/[FY25 MDAY Inspiro Credentials Log-Test.xlsb]Class dates lookup'!A:A,0)),"")
CC4:CC15	CC4	=IF(R4="Canceled","Canceled",IF(AF4="","",IF(AF4="N/A",S4-WEEKDAY(S4,3),AF4-WEEKDAY(AF4,3))))
CD4:CD15	CD4	=H4
CE4:CE15	CE4	=IF(E4="SPI","INS",E4)
CF4:CF15	CF4	=L4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F:F	Cell Value	="Y"	text	NO
A:A	Cell Value	duplicates	text	NO

FTE Sheet

CountIFsTestSheet.xlsx
	A	B	C	D	E					J					O						U	V					AA					AF						AL	AM					AR					AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX
1
2		Brand	Partner	Site	Class Type					Jul					AUG						SEPT	Q1					OCT					NOV						DEC	Q2					Jan					Feb	24-Feb	3-Mar	10-Mar	17-Mar	24-Mar	Mar	Q3	31-Mar	7-Apr	14-Apr	21-Apr	Apr	28-Apr	5-May	12-May	19-May	May	26-May	2-Jun	9-Jun	16-Jun	23-Jun	June	Q4	FY25	LY	LY
3	INS PH	AB	INS	MNL	Full S&S					-					-						52	52					123					88						62	273					-					441	-	-	-	-	-	0	441	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	766
4		AB	INS	MNL	Abbr. Sales & Full Service					-					-						-	-					0					86						454	540					-					704	-	-	-	-	-	0	704	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1,244
5		AB	INS	MNL	Abbr. S&S					2					4						20	26					16					0						0	16					45					301	-	-	-	-	-	0	346	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	388
6		HD	INS	MNL	Full S&S					-					-						10	10					0					55						0	55					-					0	-	-	-	-	-	0	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	65
7		HD	INS	MNL	Abbr. Sales & Full Service					-					-						38	38					87					50						618	755					-					0	-	-	-	-	-	0	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	793
8		HD	INS	MNL	Abbr. S&S					20					7						9	36					0					0						0	0					-					0	-	-	-	-	-	0	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	36
9	INS NIC	AB	INS	NIC	Full S&S					-					22						7	29					0					0						0	0					-					168	-	-	-	-	-	0	168	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	197
10		AB	INS	NIC	Abbr. Sales & Full Service					-					-						-	-					0					0						0	0					-					0	-	-	-	-	-	0	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
11		AB	INS	NIC	Abbr. S&S					-					-						-	-					24					0						0	24					114					25	-	-	-	-	-	0	139	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	163
12		HD	INS	NIC	Full S&S					-					-						-	-					0					92						150	242					-					0	-	-	-	-	-	0	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	242
13		HD	INS	NIC	Abbr. Sales & Full Service					-					-						35	35					0					0						0	0					-					0	-	-	-	-	-	0	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	35
14		HD	INS	NIC	Abbr. S&S					-					-						-	-					0					0						0	0					-					0	-	-	-	-	-	0	0	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
FTE

Cell Formulas
Range	Formula
U3:U14,BT3:BT14,BC3:BC14,AL3:AL14	U3	=SUM(P3:T3)
V3:V14,BU3:BU14,BD3:BD14,AM3:AM14	V3	=SUM(J3,O3,U3)
BV3:BV14	BV3	=SUM(V3,AM3,BD3,BU3)
J3:J14,BN3:BN14,BI3:BI14,AW3:AW14,AF3:AF14,AA3:AA14,O3:O14	J3	=SUM(F3:I3)

 

[etaf]

thanks for putting the 2 sheets into XL2BB
But i dont know what you want now - as the formula you showed before does not much here

so where do you want to SUM and COUNT and what do you want to SUM and COUNT

sorry , just a bit confused now

did you try rewriting/correcting the formula in your 1st post as suggested - so you dont get NAME error ???

 

[Mykahlia]

So I want to count the total of Agents based off their role, class type, brand, partner and site for a specific date range since we will run classes for several weeks. I want to break it down by each week running Monday through Sunday - Once it counts the agents based on those criteria, I want it to put the total on the FTE sheet in the corresponding week based off the class type. So example Week Begining 3/24 though 3/30 . =COUNTIFS('Master List'!CA3:CA,"Agent",'Master List'!CB3:CB,"Full S&S",'Master List'!CD3:CD,"AB",'Master List'!CE3:CE,"INS",'Master List'!CF3:CF,"MNL",'Master List'!CC3:CC,">="&DATE(2025,3,24),'Master List'!C:C,"<="&DATE(2025,3,30))

 

[etaf]

the xl2bb does not match the info in the formula

BUT did you change to use correct rows ???

=COUNTIFS('Master List'!CA3:CA1000,"Agent",'Master List'!CB3:CB1000,"Full S&S",'Master List'!CD3:CD1000,"AB",'Master List'!CE3:CE1000,"INS",'Master List'!CF3:CF1000,"MNL",'Master List'!CC3:CC1000,">="&DATE(2025,3,31),'Master List'!CC3:CC1000,"<="&DATE(2025,4,6))

Class Type
in your example -
this is column K in the Master List
and column E in the FTE
so breaking down the forumla - that would be a
=COUNTIFS('Master List'!$K$4:$K$15,FTE!E3)

BUT none of the class type in FTE E are anything like the class type in Master list K

if you change them to match you will see the count then works - so thats what you need to do

see column G

Book3
	A	B	C	D	E	F	G
1
2		Brand	Partner	Site	Class Type		COUNT OF CLASS
3	INS PH	AB	INS	MNL	Full S&S		4
4		AB	INS	MNL	Abbr. Sales & Full Service		4
5		AB	INS	MNL	Abbr. S&S		4
6		HD	INS	MNL	Full S&S		3
7		HD	INS	MNL	Abbr. Sales & Full Service		3
8		HD	INS	MNL	Abbr. S&S		3
9	INS NIC	AB	INS	NIC	Full S&S		2
10		AB	INS	NIC	Abbr. Sales & Full Service		2
11		AB	INS	NIC	Abbr. S&S		2
12		HD	INS	NIC	Full S&S		1
13		HD	INS	NIC	Abbr. Sales & Full Service		1
14		HD	INS	NIC	Abbr. S&S		1
FTE

Cell Formulas
Range	Formula
G3:G14	G3	=COUNTIFS('Master List'!K4:K15,FTE!E3)

change column to match CLASS

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Meta							Completed by BPO
2														Qualtrics only
3	Concatenate	SSC Name	Atlas First	Atlas Last	Partner	ACE?	Date Received	Brand	First Name	Last Name	Agent / Class Type	Site	Role	Email address	Qualtrics Y/N?	Base Brand	Class Name	Training Start	Graduation
4	ArnulfoAbundoJr	Arnulfo Abundo Jr	ARNULFO	ABUNDO JR	SPI	N	45735	AB	Arnulfo	Abundo Jr	Full S&S	MNL	Agent	N/A	N	V	INS Boot Camp	45741	45743
5	BrisaFernandaAreas	Brisa Fernanda Areas	BRISA FERNANDA	AREAS	SPI	N	45735	AB	Brisa Fernanda	Areas	Abbr. Sales & Full Service	MNL	Agent	N/A	N	V	INS Boot Camp	45741	45743
6	CharinaGantalao	Charina Gantalao	CHARINA	GANTALAO	SPI	N	45735	AB	Charina	Gantalao	Abbr. S&S	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
7	CheslyMartin	Chesly Martin	CHESLY	MARTIN	SPI	N	45735	AB	Chesly	Martin	Full S&S	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
8	ChristineMaePingkian	Christine Mae Pingkian	CHRISTINE MAE	PINGKIAN	SPI	N	45735	AB	Christine Mae	Pingkian	Abbr. Sales & Full Service	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
9	Ck-AnneOlivares	Ck-Anne Olivares	CK-ANNE	OLIVARES	SPI	N	45735	AB	Ck-Anne	Olivares	Abbr. S&S	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
10	CristinaPriela	Cristina Priela	CRISTINA	PRIELA	SPI	N	45735	AB	Cristina	Priela	Full S&S	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
11	DietrichRuiz	Dietrich Ruiz	DIETRICH	RUIZ	SPI	N	45735	AB	Dietrich	Ruiz	Abbr. Sales & Full Service	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
12	DionGeorgePagulayan	Dion George Pagulayan	DION GEORGE	PAGULAYAN	SPI	N	45735	AB	Dion George	Pagulayan	Abbr. S&S	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
13	EllaMaeDailo	Ella Mae Dailo	ELLA MAE	DAILO	SPI	N	45735	AB	Ella Mae	Dailo	Full S&S	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
14	ElviaSinaiEscobar	Elvia Sinai Escobar	ELVIA SINAI	ESCOBAR	SPI	N	45735	AB	Elvia Sinai	Escobar	Abbr. Sales & Full Service	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
15	ElvisDowns	Elvis Downs	ELVIS	DOWNS	SPI	N	45735	AB	Elvis	Downs	Abbr. S&S	MNL	Trainer	N/A	N	V	INS Boot Camp	45741	45743
Master List

 

[Mykahlia]

Thank you so much. The formula works good. I have another question maybe you can help with. So the Site, currently reads off MNL.. 'Master List'!$L$3:$L$3000,"MNL" is there a way to have it read different sites and still pull in to the same count. Such as if i need it to read MNL and DGT or Bacolod, Davao and IloIlo?

This is the current Formula with updated info : =COUNTIFS('Master List'!$M$3:$M$3000,"Agent",'Master List'!$P$3:$P$3000,"V",'Master List'!$H$3:$H$3000,"AB",'Master List'!$L$3:$L$3000,"MNL",'Master List'!$R$3:$R$3000,">="&DATE(2025,3,24),'Master List'!$R$3:$R$3000,"<="&DATE(2025,3,30))

 

[etaf]

if its an OR then you could just add another countifs
=COUNTIFS('Master List'!$M$3:$M$3000,"Agent",'Master List'!$P$3:$P$3000,"V",'Master List'!$H$3:$H$3000,"AB",'Master List'!$L$3:$L$3000,"MNL",'Master List'!$R$3:$R$3000,">="&DATE(2025,3,24),'Master List'!$R$3:$R$3000,"<="&DATE(2025,3,30))
+
COUNTIFS('Master List'!$M$3:$M$3000,"Agent",'Master List'!$P$3:$P$3000,"V",'Master List'!$H$3:$H$3000,"AB",'Master List'!$L$3:$L$3000,"DGT",'Master List'!$R$3:$R$3000,">="&DATE(2025,3,24),'Master List'!$R$3:$R$3000,"<="&DATE(2025,3,30))

or then add various countifs for all the possible different L columns

OR
you can use { } for the different items as an OR
but would need to use SUM at the beginning
so

=SUM( COUNTIFS('Master List'!$M$3:$M$3000,"Agent",'Master List'!$P$3:$P$3000,"V",'Master List'!$H$3:$H$3000,"AB",'Master List'!$L$3:$L$3000,{"DGT","MNL", "Bacolod", "Davao", "IloIlo"} ,'Master List'!$R$3:$R$3000,">="&DATE(2025,3,24),'Master List'!$R$3:$R$3000,"<="&DATE(2025,3,30))

 

[Mykahlia]

Thank you so very much for your help. This worked perfectly!

 

[etaf]

you are welcome