Need help with formula Countifs of Sumproduct (or something else)

M

Magic Man

New Member
Joined
Oct 20, 2015
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hi All,

I'm not sure if this needs a Countif formula, a Sumproduct formula or something else.
I have the following sheet and I'm tying to do a count based on multiple criteria

I need to count based on the following Criteria in the Sheet "Trg Register"

COUNTING CRITERIA
In sheet "Trg Register" Column E contains "GDPR" OR "Data Protection"; AND
In sheet "Trg Register" Column O is equal to F4 AND
In sheet "Trg Register" Column S is equal to A5 AND
In sheet "Trg Register" Column H is not equal to "Expired"

Try as I might, I can't seem to be able to upload an image or the XL2BB file.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
I do believe I've managed to upload the image but it's very small
 

Attachments

  • Mr Excel Sample.jpg
    Mr Excel Sample.jpg
    70.9 KB · Views: 8
  • Mr Excel Sample 2.jpg
    Mr Excel Sample 2.jpg
    57.3 KB · Views: 9
Upvote 0
FirstnameSurnameEmployeeNumberUsernameCourseVersionTitleCourseStatusDateCompletedHasExpiredRenewalRecommendedCompletion DeadlineAssignedDateAllocatedLastAccessedEmailAddressDapartmentStatusScoreLTA Status
KobeHodges113080304023368GDPR EU: Essentialsnot_started31/05/202207/06/2022Kobe.Hodges@email.comHR Overdue11
DwaynePineda168980624023371GDPR EU: Essentialsnot_started31/05/202207/06/2022Dwayne.Pineda@email.comPayrollOverdue12
MiguelFrey145239103944857GDPR UK: Advancednot_started31/07/202210/06/2022Miguel.Frey@email.comAdminNot Attempted1001
CharlotteRush125004804023372GDPR EU: Essentialsnot_started31/07/202207/06/2022Charlotte.Rush@email.comITNot Attempted1002
SalvadorBarker147599674023383GDPR EU: Essentialsnot_started31/07/202207/06/2022Salvador.Barker@email.comITNot Attempted1003
LilianaKnox131965804023389GDPR EU: Advancednot_started31/07/202207/06/2022Liliana.Knox@email.comHRNot Attempted1004
EugeneSimon190943884022691Cyber Security Awarenessnot_started31/07/202207/06/2022Eugene.Simon@email.comHRNot Attempted1005
KayleighCopeland131821433793363Cyber Security Awarenessnot_started31/07/202204/03/2022Kayleigh.Copeland@email.comHRNot Attempted1006
NoraConway12950395Cyber Security AwarenessNot Attempted02/09/2021Nora.Conway@email.comAdminNot Attempted1007
TatianaBryant10224662Data Protection V9Not Attempted13/08/2021Tatiana.Bryant@email.comITNot Attempted1008
SummerRoman17311557Data Protection V9Not Attempted15/07/2021Summer.Roman@email.comFinanceNot Attempted1009
BraedenPalmer17989211Data Protection V9Not Attempted03/06/2021Braeden.Palmer@email.comAccount ManagementNot Attempted1010
JoyAguilar18295206Data Protection V8Incomplete09/11/202113/12/2021Joy.Aguilar@email.comFinanceincomplete10001
ChanaSalas13781461Data Protection V8Incomplete09/11/202121/04/2022Chana.Salas@email.comHRincomplete10002
SagePoole10948877Data Protection V8Incomplete25/02/202225/02/2022Sage.Poole@email.comPayrollincomplete10003
LoganClarke166201944033999GDPR UK: Advancedin_progress31/07/202210/06/202227/06/2022Logan.Clarke@email.comAccount Managementincomplete10004
KarlieMitchell137349694023376GDPR EU: Essentialsin_progress31/07/202207/06/202227/06/2022Karlie.Mitchell@email.comPayrollincomplete10005
BaileyVelez163104253869849GDPR UK: Advancedin_progress31/07/202201/04/202201/04/2022Bailey.Velez@email.comFinanceincomplete10006
WendySantiago141012373844815GDPR UK: Advancedin_progress31/07/202223/03/202223/03/2022Wendy.Santiago@email.comAccount Managementincomplete10007
JabariVargas163619223793402GDPR UK: Advancedin_progress31/07/202204/03/202206/03/2022Jabari.Vargas@email.comAdminincomplete10008
JaidynPennington140392293944856GDPR UK: Advancedcomplete29/06/202231/07/202210/06/202229/06/2022Jaidyn.Pennington@email.comFinanceComplete100001
KoltonHarris173626374033994GDPR UK: Advancedcomplete29/06/202231/07/202210/06/202229/06/2022Kolton.Harris@email.comITComplete100002
EileenDyer124372944022686GDPR EU: Essentialscomplete21/06/202231/07/202207/06/202221/06/2022Eileen.Dyer@email.comHR Complete100003
HoldenDodson163753084033991GDPR UK: Advancedcomplete21/06/202231/07/202210/06/202221/06/2022Holden.Dodson@email.comPayrollComplete100004
KaedenChoi153893164036768GDPR UK: Advancedcomplete21/06/202213/06/202221/06/2022Kaeden.Choi@email.comITComplete100005
AlannaHudson179382953945521GDPR UK: Advancedcomplete21/06/202231/07/202210/06/202221/06/2022Alanna.Hudson@email.comITComplete100006
JaquanHale131430884022682GDPR EU: Essentialscomplete21/06/202231/07/202207/06/202221/06/2022Jaquan.Hale@email.comHRComplete100007
ElainaSalazar109014364034004GDPR UK: Advancedcomplete15/06/202231/07/202210/06/202215/06/2022Elaina.Salazar@email.comHRComplete100008
FrancescaSampson142382194034002GDPR UK: Advancedcomplete15/06/202231/07/202210/06/202215/06/2022Francesca.Sampson@email.comHRComplete100009
KamariWoodard149766274022684GDPR EU: Essentialscomplete14/06/202207/06/202214/06/2022Kamari.Woodard@email.comAdminComplete100010
EddieCameron152413224022696GDPR EU: Essentialscomplete14/06/202207/06/202214/06/2022Eddie.Cameron@email.comFinanceComplete100011
MarlieHaney121843454022687GDPR EU: Essentialscomplete13/06/202207/06/202213/06/2022Marlie.Haney@email.comHRComplete100012
MaddisonHumphrey153390194033996GDPR UK: Advancedcomplete11/06/202210/06/202211/06/2022Maddison.Humphrey@email.comPayrollComplete100013
DanielaLivingston134098733944853GDPR UK: Advancedcomplete11/06/202210/06/202211/06/2022Daniela.Livingston@email.comAccount ManagementComplete100014
PriscillaWalter164420133945523GDPR UK: Advancedcomplete10/06/202231/07/202210/06/202210/06/2022Priscilla.Walter@email.comPayrollComplete100015
AlexiaMontes111086824033993GDPR UK: Advancedcomplete10/06/202231/07/202210/06/202210/06/2022Alexia.Montes@email.comFinanceComplete100016
LaylahMills102497154022695GDPR EU: Essentialscomplete10/06/202231/07/202207/06/202210/06/2022Laylah.Mills@email.comPayrollComplete100017
DrakeSuarez17290842Data Protection V9Certificate Sent09/06/202231/07/202212/04/202209/06/2022Drake.Suarez@email.comFinanceComplete100018
DravenCarey155211914022690GDPR EU: Essentialscomplete08/06/202231/07/202207/06/202208/06/2022Draven.Carey@email.comAccount ManagementComplete100019
LaurynSoto113580684022700GDPR EU: Essentialscomplete08/06/202231/07/202207/06/202208/06/2022Lauryn.Soto@email.comAdminComplete100020
JohannaBishop155686424023380GDPR EU: Essentialscomplete08/06/202231/07/202207/06/202208/06/2022Johanna.Bishop@email.comFinanceComplete100021
XimenaBolton145488864022692GDPR EU: Essentialscomplete07/06/202231/07/202207/06/202207/06/2022Ximena.Bolton@email.comITComplete100022
CamillaGamble11546136Data Protection V8Certificate Sent31/05/202215/07/202131/05/2022Camilla.Gamble@email.comHR Complete100023
JakeHerrera18486956Data Protection V9Certificate Sent27/05/202229/04/202227/05/2022Jake.Herrera@email.comPayrollComplete100024
MikaylaWoods18057856Data Protection V9Certificate Sent26/07/2020Due for renewal19/05/202226/05/2022Mikayla.Woods@email.comITComplete100025
LailaMaynard17872076Data Protection V9Certificate Sent20/05/202229/04/202220/05/2022Laila.Maynard@email.comITComplete100026
MarcelStephenson137689713944857GDPR UK: Essentialscomplete20/07/2020Due for renewal31/07/202204/05/202220/05/2022Marcel.Stephenson@email.comHRComplete100027
CordellHuang15073521Data Protection V8Certificate Sent25/02/2020Expired12/08/202125/02/2022Cordell.Huang@email.comHRComplete100028
LilaDaniel18382227Data Protection V8Certificate Sent25/02/2020Expired10/01/202225/02/2022Lila.Daniel@email.comHRComplete100029
 
Upvote 0
This should work:
Excel Formula: 
=COUNTIFS('Trg Register'!E:E,"GDPR",'Trg Register'!O:O,F4,'Trg Register'!S:S,A5,'Trg Register'!H:H,"Expired")
+COUNTIFS('Trg Register'!E:E,"Data Protection",'Trg Register'!O:O,F4,'Trg Register'!S:S,A5,'Trg Register'!H:H,"Expired")

You can try this but it may only work in MS 365
Excel Formula: 
=SUM(COUNTIFS('Trg Register'!E:E,{"GDPR","Data Protection"},'Trg Register'!O:O,F4,'Trg Register'!S:S,A5,'Trg Register'!H:H,"Expired"))
 
Upvote 0
Alex Blakenburg said:
This should work:
Excel Formula: 
=COUNTIFS('Trg Register'!E:E,"GDPR",'Trg Register'!O:O,F4,'Trg Register'!S:S,A5,'Trg Register'!H:H,"Expired")
+COUNTIFS('Trg Register'!E:E,"Data Protection",'Trg Register'!O:O,F4,'Trg Register'!S:S,A5,'Trg Register'!H:H,"Expired")

You can try this but it may only work in MS 365
Excel Formula: 
=SUM(COUNTIFS('Trg Register'!E:E,{"GDPR","Data Protection"},'Trg Register'!O:O,F4,'Trg Register'!S:S,A5,'Trg Register'!H:H,"Expired"))
Click to expand...
Thanks Alex,

You were right the MS365 formula didn't work.

Think you missed the bit where I indicated that Col H;H IS NOT equal to expired. Cracked it though with adding "<>" to the relevant part:
Excel Formula: 
=COUNTIFS('Trg Register'!E:E,"GDPR*",'Trg Register'!O:O,$F$4,'Trg Register'!S:S,$A5,'Trg Register'!H:H,"<>Expired")+COUNTIFS('Trg Register'!E:E,"Data Protection*",'Trg Register'!O:O,$F$4,'Trg Register'!S:S,$A5,'Trg Register'!H:H,"<>Expired")

Thanks again,
 
Upvote 0
You must log in or register to reply here.

Similar threads

dezibluenose
  • Solved
Countifs formula not picking up dates, when reference columns show as dates?!
Replies
7
Views
420
Alex Blakenburg
A
E
  • Solved
COUNTIF and SUMIF formula with multiple criteria
Replies
2
Views
722
excelnewb221
E
M
  • Question Question
Unique or Sumproduct or something else?
Replies
6
Views
528
Fluff
Fluff
lost_in_the_sauce
  • Question Question
SumProduct works but locks my laptop, need something less memory intensive
Replies
2
Views
285
MARK858
MARK858
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
272
sijpie
sijpie

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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