SumIFs or CountIFs Formula help.

M

Mykahlia

New Member
Joined
Feb 17, 2025
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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.


1742498523623.png


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

1742498654864.png
 
Sort by date Sort by votes
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.
www.mrexcel.com

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 www.mrexcel.com

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

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 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
 
Upvote 0
CountIFsTestSheet.xlsx
ABCDEFGHIJKLMNOPQRSTBZCACBCCCDCECF
1MetaCompleted by BPOFTE Sheet
2Qualtrics only
3ConcatenateSSC NameAtlas FirstAtlas LastPartnerACE?Date ReceivedBrandFirst NameLast NameAgent / Class TypeSiteRoleEmail address Qualtrics Y/N?Base BrandClass NameTraining StartGraduationRoleClass typeWeek BeginningBrandPartnerSite
4ArnulfoAbundoJrArnulfo Abundo JrARNULFOABUNDO JRSPIN3/19/2025ABArnulfoAbundo JrBoot CampMNLAgentN/ANVINS Boot Camp3/25/20253/27/2025AgentFull S&S3/24/2025ABINSMNL
5BrisaFernandaAreasBrisa Fernanda AreasBRISA FERNANDAAREASSPIN3/19/2025ABBrisa FernandaAreasBoot CampMNLAgentN/ANVINS Boot Camp3/25/20253/27/2025AgentFull S&S3/24/2025ABINSMNL
6CharinaGantalaoCharina GantalaoCHARINAGANTALAOSPIN3/19/2025ABCharinaGantalaoBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
7CheslyMartinChesly MartinCHESLYMARTINSPIN3/19/2025ABCheslyMartinBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
8ChristineMaePingkianChristine Mae PingkianCHRISTINE MAEPINGKIANSPIN3/19/2025ABChristine MaePingkianBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
9Ck-AnneOlivaresCk-Anne OlivaresCK-ANNEOLIVARESSPIN3/19/2025ABCk-AnneOlivaresBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
10CristinaPrielaCristina PrielaCRISTINAPRIELASPIN3/19/2025ABCristinaPrielaBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
11DietrichRuizDietrich RuizDIETRICHRUIZSPIN3/19/2025ABDietrichRuizBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
12DionGeorgePagulayanDion George PagulayanDION GEORGEPAGULAYANSPIN3/19/2025ABDion GeorgePagulayanBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
13EllaMaeDailoElla Mae DailoELLA MAEDAILOSPIN3/19/2025ABElla MaeDailoBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
14ElviaSinaiEscobarElvia Sinai EscobarELVIA SINAIESCOBARSPIN3/19/2025ABElvia SinaiEscobarBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
15ElvisDownsElvis DownsELVISDOWNSSPIN3/19/2025ABElvisDownsBoot CampMNLTrainerN/ANVINS Boot Camp3/25/20253/27/2025TrainerFull S&S3/24/2025ABINSMNL
Master List
Cell Formulas
RangeFormula
CA4:CA15CA4=M4
CB4:CB15CB4=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:CC15CC4=IF(R4="Canceled","Canceled",IF(AF4="","",IF(AF4="N/A",S4-WEEKDAY(S4,3),AF4-WEEKDAY(AF4,3))))
CD4:CD15CD4=H4
CE4:CE15CE4=IF(E4="SPI","INS",E4)
CF4:CF15CF4=L4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F:FCell Value="Y"textNO
A:ACell ValueduplicatestextNO



FTE Sheet

CountIFsTestSheet.xlsx
ABCDEJOUVAAAFALAMARAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBX
1
2BrandPartnerSiteClass TypeJulAUGSEPTQ1OCTNOVDECQ2JanFeb24-Feb3-Mar10-Mar17-Mar24-MarMarQ331-Mar7-Apr14-Apr21-AprApr28-Apr5-May12-May19-MayMay26-May2-Jun9-Jun16-Jun23-JunJuneQ4FY25LYLY
3INS PHABINSMNLFull S&S--52521238862273 - 441 - - - - - 0441 - - - - - - - - - - - - - - - --766
4ABINSMNLAbbr. Sales & Full Service----086454540 - 704 - - - - - 0704 - - - - - - - - - - - - - - - --1,244
5ABINSMNLAbbr. S&S24202616001645301 - - - - - 0346 - - - - - - - - - - - - - - - --388
6HDINSMNLFull S&S--1010055055 - 0 - - - - - 00 - - - - - - - - - - - - - - - --65
7HDINSMNLAbbr. Sales & Full Service--38388750618755 - 0 - - - - - 00 - - - - - - - - - - - - - - - --793
8HDINSMNLAbbr. S&S2079360000 - 0 - - - - - 00 - - - - - - - - - - - - - - - --36
9INS NICABINSNICFull S&S-227290000 - 168 - - - - - 0168 - - - - - - - - - - - - - - - --197
10ABINSNICAbbr. Sales & Full Service----0000 - 0 - - - - - 00 - - - - - - - - - - - - - - - ---
11ABINSNICAbbr. S&S----24002411425 - - - - - 0139 - - - - - - - - - - - - - - - --163
12HDINSNICFull S&S----092150242 - 0 - - - - - 00 - - - - - - - - - - - - - - - --242
13HDINSNICAbbr. Sales & Full Service--35350000 - 0 - - - - - 00 - - - - - - - - - - - - - - - --35
14HDINSNICAbbr. S&S----0000 - 0 - - - - - 00 - - - - - - - - - - - - - - - ---
FTE
Cell Formulas
RangeFormula
U3:U14,BT3:BT14,BC3:BC14,AL3:AL14U3=SUM(P3:T3)
V3:V14,BU3:BU14,BD3:BD14,AM3:AM14V3=SUM(J3,O3,U3)
BV3:BV14BV3=SUM(V3,AM3,BD3,BU3)
J3:J14,BN3:BN14,BI3:BI14,AW3:AW14,AF3:AF14,AA3:AA14,O3:O14J3=SUM(F3:I3)
 
Upvote 0
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 ???
 
Upvote 0
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))
 
Upvote 0
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
ABCDEFG
1
2BrandPartnerSiteClass TypeCOUNT OF CLASS
3INS PHABINSMNLFull S&S4
4ABINSMNLAbbr. Sales & Full Service4
5ABINSMNLAbbr. S&S4
6HDINSMNLFull S&S3
7HDINSMNLAbbr. Sales & Full Service3
8HDINSMNLAbbr. S&S3
9INS NICABINSNICFull S&S2
10ABINSNICAbbr. Sales & Full Service2
11ABINSNICAbbr. S&S2
12HDINSNICFull S&S1
13HDINSNICAbbr. Sales & Full Service1
14HDINSNICAbbr. S&S1
FTE
Cell Formulas
RangeFormula
G3:G14G3=COUNTIFS('Master List'!K4:K15,FTE!E3)



change column to match CLASS
Book3
ABCDEFGHIJKLMNOPQRS
1MetaCompleted by BPO
2Qualtrics only
3ConcatenateSSC NameAtlas FirstAtlas LastPartnerACE?Date ReceivedBrandFirst NameLast NameAgent / Class TypeSiteRoleEmail addressQualtrics Y/N?Base BrandClass NameTraining StartGraduation
4ArnulfoAbundoJrArnulfo Abundo JrARNULFOABUNDO JRSPIN45735ABArnulfoAbundo JrFull S&SMNLAgentN/ANVINS Boot Camp4574145743
5BrisaFernandaAreasBrisa Fernanda AreasBRISA FERNANDAAREASSPIN45735ABBrisa FernandaAreasAbbr. Sales & Full ServiceMNLAgentN/ANVINS Boot Camp4574145743
6CharinaGantalaoCharina GantalaoCHARINAGANTALAOSPIN45735ABCharinaGantalaoAbbr. S&SMNLTrainerN/ANVINS Boot Camp4574145743
7CheslyMartinChesly MartinCHESLYMARTINSPIN45735ABCheslyMartinFull S&SMNLTrainerN/ANVINS Boot Camp4574145743
8ChristineMaePingkianChristine Mae PingkianCHRISTINE MAEPINGKIANSPIN45735ABChristine MaePingkianAbbr. Sales & Full ServiceMNLTrainerN/ANVINS Boot Camp4574145743
9Ck-AnneOlivaresCk-Anne OlivaresCK-ANNEOLIVARESSPIN45735ABCk-AnneOlivaresAbbr. S&SMNLTrainerN/ANVINS Boot Camp4574145743
10CristinaPrielaCristina PrielaCRISTINAPRIELASPIN45735ABCristinaPrielaFull S&SMNLTrainerN/ANVINS Boot Camp4574145743
11DietrichRuizDietrich RuizDIETRICHRUIZSPIN45735ABDietrichRuizAbbr. Sales & Full ServiceMNLTrainerN/ANVINS Boot Camp4574145743
12DionGeorgePagulayanDion George PagulayanDION GEORGEPAGULAYANSPIN45735ABDion GeorgePagulayanAbbr. S&SMNLTrainerN/ANVINS Boot Camp4574145743
13EllaMaeDailoElla Mae DailoELLA MAEDAILOSPIN45735ABElla MaeDailoFull S&SMNLTrainerN/ANVINS Boot Camp4574145743
14ElviaSinaiEscobarElvia Sinai EscobarELVIA SINAIESCOBARSPIN45735ABElvia SinaiEscobarAbbr. Sales & Full ServiceMNLTrainerN/ANVINS Boot Camp4574145743
15ElvisDownsElvis DownsELVISDOWNSSPIN45735ABElvisDownsAbbr. S&SMNLTrainerN/ANVINS Boot Camp4574145743
Master List
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Locked
  • Question Question
Severe Amateur needs code to pull data from multiple sheets into one master
Replies
1
Views
282
Peter_SSs
Peter_SSs
H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
387
helpcharity
H
P
  • Question Question
SUMIF formula to pull data from one tab to another with a column date range, type, and totals
Replies
5
Views
162
kvsrinivasamurthy
K
E
  • Solved
Formula help
Replies
5
Views
154
Alex Blakenburg
A
R
  • Question Question
Help Formula Countif or Countifs
Replies
6
Views
164
Cubist
Cubist

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top