SUMPRODUCT with nested If statement

M

Moeey

New Member
Joined
Sep 13, 2014
Messages
34
Hi everyone,
Hope you're all well.

I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT formula.

The formula is trying to count all the entries in "HRData" table, for the specified year and "Job Band".
If the job band selected is "All Employees" then count all the entries for the specified year.
If the job band selected is not "All Employees" then only count the selected "Job Band" for the specified year.
The formula only seems to work for "All Employees".
There are no "errors" for the formula.

I think the problem lies with the "else" part of the IF statement.
From what I can gather, the "else" part of the IF statement, is only reading one row of the table.

Here's the formula,
=SUMPRODUCT((YEAR(HRData[Start Date])=$A$8)*(IF($B$8="All Employees",TRUE(),HRData[Job Band]=$B$8)))

1601042599535.png


This is what my "HRData" table looks like.
There are 10,000 entries in the table.
1601043192201.png


I hope this makes sense.
Thanks for looking.

Mo
 

Attachments

  • 1601041946158.png
    1601041946158.png
    12.6 KB · Views: 8
  • 1601042007603.png
    1601042007603.png
    28.5 KB · Views: 7
Sort by date Sort by votes
In that case I suspect that the problem may well be leading/trailing spaces in your data.
Although that wouldn't explain why Norie's formula gives a different answer to yours.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Here's the first 100 rows
Dashboard_Training_HR_Dashboard.xlsm
ABCDEFGHIJKLM
1Emp IDParticipant NameJob BandBusiness UnitRegionTraining NameTraining TypeTrainer TypeTrainer NameStart DateEnd Date# of DaysOnline/Classroom
2TE - 01Employee - 01MMCorporateUSAdvanced Management SkillsProfessional DevelopmentOnlineJoe9-Jul-1310-Jul-132Online
3TE - 02Employee - 02JMCorporateAsia-PacificBusiness WritingProfessional DevelopmentOnlineMartha11-Sep-1212-Sep-122Internal
4TE - 03Employee - 03JMOthersCanada7 Habits of Highly Effective ExecutivesProfessional DevelopmentExternalStephen28-May-1229-May-122Online
5TE - 04Employee - 04JMMarketingEuropeCreativity and InnovationProfessional DevelopmentExternalCharles27-May-1327-May-131Internal
6TE - 05Employee - 05JMSupply ChainAsia-Pacific7 Habits of Highly Effective ExecutivesProfessional DevelopmentExternalStephen21-Jan-1421-Jan-141Internal
7TE - 06Employee - 06JMLogisticsAsia-PacificBusiness GrammarSkill DevelopmentInternalGrace21-Dec-1221-Dec-121Internal
8TE - 07Employee - 07JMSupply ChainUSSix SigmaSkill DevelopmentInternalTom6-Mar-127-Mar-122External
9TE - 08Employee - 08JMLogisticsCanadaCommunicate to ImpactProfessional DevelopmentExternalGreg3-Feb-123-Feb-121External
10TE - 09Employee - 09JMSupply ChainCanadaCreativity and InnovationProfessional DevelopmentExternalCharles27-Mar-1228-Mar-122External
11TE - 10Employee - 10JMHuman ResourceCanadaAdvanced Management SkillsProfessional DevelopmentOnlineJoe8-Apr-148-Apr-141External
12TE - 11Employee - 11JMSupply ChainCanadaMicrosoft ExcelSkill DevelopmentInternalRay5-Nov-145-Nov-141External
13TE - 12Employee - 12MMSalesCanadaPath to Extraordinary ProductivityProfessional DevelopmentExternalLouise7-May-148-May-142Internal
14TE - 13Employee - 13MMMarketingEuropeBusiness GrammarSkill DevelopmentExternalGrace31-Jan-1331-Jan-131Online
15TE - 14Employee - 14JMMarketingCanadaMicrosoft ExcelSkill DevelopmentExternalRay9-Sep-1410-Sep-142Online
16TE - 15Employee - 15SMCorporateUSAdvanced Management SkillsProfessional DevelopmentInternalJoe20-Feb-1321-Feb-132External
17TE - 16Employee - 16SMLegalAsia-PacificMicrosoft PowerpointSkill DevelopmentExternalPaul21-May-1323-May-133External
18TE - 17Employee - 17SMSalesAsia-PacificBusiness WritingProfessional DevelopmentInternalMartha18-Sep-1319-Sep-132Online
19TE - 18Employee - 18MMSalesAsia-PacificCommunicate to ImpactProfessional DevelopmentOnlineGreg23-Sep-1423-Sep-141External
20TE - 19Employee - 19MMLegalEuropeCommunicate to ImpactProfessional DevelopmentInternalGreg21-Aug-1322-Aug-132Online
21TE - 20Employee - 20MMOthersAsia-PacificBusiness WritingProfessional DevelopmentExternalMartha30-May-1230-May-121Internal
22TE - 21Employee - 21SMLegalEuropeMicrosoft ExcelSkill DevelopmentExternalRay10-Feb-1510-Feb-151Internal
23TE - 22Employee - 22MMHuman ResourceEuropeMicrosoft ExcelSkill DevelopmentInternalRay12-Jun-1413-Jun-142Online
24TE - 23Employee - 23MMLegalCanadaMicrosoft ExcelSkill DevelopmentExternalRay6-Dec-126-Dec-121External
25TE - 24Employee - 24MMLogisticsAsia-PacificMicrosoft ExcelSkill DevelopmentInternalRay9-Feb-159-Feb-151External
26TE - 25Employee - 25MMLogisticsCanada7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen13-Dec-1313-Dec-131Internal
27TE - 26Employee - 26MMMarketingAsia-PacificBusiness WritingProfessional DevelopmentOnlineMartha20-Nov-1222-Nov-123Online
28TE - 27Employee - 27SMSupply ChainCanadaMicrosoft ExcelSkill DevelopmentExternalRay17-Oct-1417-Oct-141External
29TE - 28Employee - 28MMCorporateEurope7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen24-Sep-1326-Sep-133External
30TE - 29Employee - 29MMHuman ResourceCanadaSix SigmaSkill DevelopmentExternalTom11-Oct-1311-Oct-131Online
31TE - 30Employee - 30MMCorporateAsia-PacificCommunicate to ImpactProfessional DevelopmentInternalGreg30-Apr-132-May-133Internal
32TE - 31Employee - 31JMSupply ChainCanadaPath to Extraordinary ProductivityProfessional DevelopmentOnlineLouise7-May-157-May-151Online
33TE - 32Employee - 32SMSupply ChainAsia-Pacific7 Habits of Highly Effective ExecutivesProfessional DevelopmentOnlineStephen4-Mar-136-Mar-133Internal
34TE - 33Employee - 33JMHuman ResourceUSMicrosoft PowerpointSkill DevelopmentOnlinePaul4-Feb-145-Feb-142External
35TE - 34Employee - 34JMOthersUSCreativity and InnovationProfessional DevelopmentOnlineCharles14-Nov-1314-Nov-131Internal
36TE - 35Employee - 35JMSalesCanadaCreativity and InnovationProfessional DevelopmentInternalCharles19-Feb-1419-Feb-141Internal
37TE - 36Employee - 36JMSupply ChainUSBusiness WritingProfessional DevelopmentOnlineMartha22-Nov-1322-Nov-131Internal
38TE - 37Employee - 37JMMarketingUSPath to Extraordinary ProductivityProfessional DevelopmentOnlineLouise10-Mar-1512-Mar-153Internal
39TE - 38Employee - 38JMLogisticsUS7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen17-Oct-1417-Oct-141Online
40TE - 39Employee - 39MMLogisticsCanadaCommunicate to ImpactProfessional DevelopmentInternalGreg25-Jun-1426-Jun-142Online
41TE - 40Employee - 40JMLegalCanadaBusiness GrammarSkill DevelopmentInternalGrace23-Jul-1223-Jul-121Internal
42TE - 41Employee - 41JMLogisticsAsia-PacificPath to Extraordinary ProductivityProfessional DevelopmentInternalLouise26-Dec-1226-Dec-121External
43TE - 42Employee - 42JMLegalCanadaCommunicate to ImpactProfessional DevelopmentInternalGreg10-Jul-1411-Jul-142Online
44TE - 43Employee - 43JMLegalUSMicrosoft PowerpointSkill DevelopmentOnlinePaul16-Apr-1418-Apr-143Online
45TE - 44Employee - 44JMLogisticsEuropeBusiness WritingProfessional DevelopmentOnlineMartha28-Jan-1430-Jan-143Online
46TE - 45Employee - 45JMSupply ChainEuropeMicrosoft PowerpointSkill DevelopmentExternalPaul6-Jun-146-Jun-141Online
47TE - 46Employee - 46JMOthersEuropeCommunicate to ImpactProfessional DevelopmentOnlineGreg2-Feb-154-Feb-153Internal
48TE - 47Employee - 47JMSupply ChainEuropeMicrosoft ExcelSkill DevelopmentOnlineRay2-Sep-142-Sep-141Internal
49TE - 48Employee - 48JMOthersUSMicrosoft PowerpointSkill DevelopmentExternalPaul3-Apr-143-Apr-141Internal
50TE - 49Employee - 49JMCorporateEuropeMicrosoft PowerpointSkill DevelopmentExternalPaul5-May-156-May-152Internal
51TE - 50Employee - 50JMLogisticsAsia-PacificMicrosoft PowerpointSkill DevelopmentExternalPaul17-Nov-1418-Nov-142Internal
52TE - 51Employee - 51JMLegalCanadaBusiness GrammarSkill DevelopmentInternalGrace23-Dec-1424-Dec-142External
53TE - 52Employee - 52MMSupply ChainAsia-PacificBusiness WritingProfessional DevelopmentExternalMartha28-Nov-1329-Nov-132External
54TE - 53Employee - 53MMLogisticsAsia-PacificCreativity and InnovationProfessional DevelopmentExternalCharles1-Apr-132-Apr-132Internal
55TE - 54Employee - 54JMOthersEuropeBusiness WritingProfessional DevelopmentInternalMartha13-Nov-1313-Nov-131Internal
56TE - 55Employee - 55JMLegalCanadaMicrosoft PowerpointSkill DevelopmentInternalPaul21-Jun-1221-Jun-121Internal
57TE - 56Employee - 56JMOthersAsia-PacificCommunicate to ImpactProfessional DevelopmentOnlineGreg21-May-1522-May-152Online
58TE - 57Employee - 57JMHuman ResourceAsia-Pacific7 Habits of Highly Effective ExecutivesProfessional DevelopmentOnlineStephen23-Dec-1423-Dec-141Internal
59TE - 58Employee - 58JMLegalAsia-PacificCreativity and InnovationProfessional DevelopmentOnlineCharles15-Jul-1416-Jul-142Online
60TE - 59Employee - 59JMOthersEuropeSix SigmaSkill DevelopmentInternalTom6-Feb-126-Feb-121Online
61TE - 60Employee - 60JMMarketingUSPath to Extraordinary ProductivityProfessional DevelopmentOnlineLouise28-Jan-1429-Jan-142Online
62TE - 61Employee - 61JMSalesUS7 Habits of Highly Effective ExecutivesProfessional DevelopmentOnlineStephen31-Oct-1431-Oct-141External
63TE - 62Employee - 62JMOthersEuropeBusiness GrammarSkill DevelopmentOnlineGrace11-Jul-1411-Jul-141External
64TE - 63Employee - 63JMLegalEuropeMicrosoft ExcelSkill DevelopmentExternalRay10-May-1310-May-131Online
65TE - 64Employee - 64JMSupply ChainAsia-PacificAdvanced Management SkillsProfessional DevelopmentOnlineJoe15-Jul-1417-Jul-143External
66TE - 65Employee - 65JMSalesEuropeBusiness WritingProfessional DevelopmentExternalMartha9-Apr-1211-Apr-123Internal
67TE - 66Employee - 66JMSalesAsia-PacificBusiness WritingProfessional DevelopmentInternalMartha25-Sep-1227-Sep-123Internal
68TE - 67Employee - 67JMLogisticsAsia-PacificMicrosoft PowerpointSkill DevelopmentExternalPaul12-Jun-1214-Jun-123Online
69TE - 68Employee - 68JMHuman ResourceUSCreativity and InnovationProfessional DevelopmentInternalCharles17-Sep-1419-Sep-143Internal
70TE - 69Employee - 69JMLogisticsEuropeAdvanced Management SkillsProfessional DevelopmentInternalJoe5-Jun-146-Jun-142External
71TE - 70Employee - 70JMSupply ChainAsia-PacificCreativity and InnovationProfessional DevelopmentExternalCharles21-May-1221-May-121External
72TE - 71Employee - 71JMSupply ChainCanadaPath to Extraordinary ProductivityProfessional DevelopmentInternalLouise17-Jul-1319-Jul-133External
73TE - 72Employee - 72JMMarketingEuropeBusiness WritingProfessional DevelopmentInternalMartha3-Feb-123-Feb-121External
74TE - 73Employee - 73JMLegalCanadaMicrosoft PowerpointSkill DevelopmentExternalPaul29-Apr-1330-Apr-132Internal
75TE - 74Employee - 74SMCorporateAsia-PacificMicrosoft PowerpointSkill DevelopmentInternalPaul16-Mar-1516-Mar-151External
76TE - 75Employee - 75MMOthersEuropePath to Extraordinary ProductivityProfessional DevelopmentOnlineLouise10-Apr-1310-Apr-131Online
77TE - 76Employee - 76MMSupply ChainEuropeCreativity and InnovationProfessional DevelopmentOnlineCharles24-Jan-1226-Jan-123Internal
78TE - 77Employee - 77MMHuman ResourceUSBusiness WritingProfessional DevelopmentExternalMartha12-May-1412-May-141Internal
79TE - 78Employee - 78JMHuman ResourceUSAdvanced Management SkillsProfessional DevelopmentInternalJoe24-Jul-1226-Jul-123Internal
80TE - 79Employee - 79MMMarketingCanadaMicrosoft PowerpointSkill DevelopmentInternalPaul30-Oct-1430-Oct-141External
81TE - 80Employee - 80JMMarketingUS7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen18-Sep-1220-Sep-123External
82TE - 81Employee - 81MMHuman ResourceAsia-PacificBusiness GrammarSkill DevelopmentInternalGrace1-Sep-143-Sep-143External
83TE - 82Employee - 82MMLegalAsia-PacificBusiness WritingProfessional DevelopmentOnlineMartha13-Jun-1213-Jun-121External
84TE - 83Employee - 83JMOthersEuropeBusiness GrammarSkill DevelopmentExternalGrace25-Jan-1226-Jan-122External
85TE - 84Employee - 84MMLegalAsia-PacificAdvanced Management SkillsProfessional DevelopmentOnlineJoe25-Apr-1225-Apr-121External
86TE - 85Employee - 85JMMarketingUS7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen24-Apr-1524-Apr-151Internal
87TE - 86Employee - 86MMLogisticsEuropeMicrosoft ExcelSkill DevelopmentExternalRay26-Sep-1426-Sep-141External
88TE - 87Employee - 87JMSupply ChainUSPath to Extraordinary ProductivityProfessional DevelopmentExternalLouise19-Feb-1419-Feb-141Internal
89TE - 88Employee - 88JMSupply ChainCanada7 Habits of Highly Effective ExecutivesProfessional DevelopmentExternalStephen30-Jul-1331-Jul-132Internal
90TE - 89Employee - 89JMCorporateUSSix SigmaSkill DevelopmentExternalTom12-Feb-1513-Feb-152Internal
91TE - 90Employee - 90JMCorporateEuropeMicrosoft PowerpointSkill DevelopmentInternalPaul26-Mar-1228-Mar-123Internal
92TE - 91Employee - 91JMOthersUSMicrosoft PowerpointSkill DevelopmentInternalPaul21-Apr-1421-Apr-141External
93TE - 92Employee - 92JMLegalEuropeCommunicate to ImpactProfessional DevelopmentOnlineGreg25-Feb-1327-Feb-133Online
94TE - 93Employee - 93JMSalesEuropeCommunicate to ImpactProfessional DevelopmentInternalGreg12-Apr-1213-Apr-122External
95TE - 94Employee - 94MMSupply ChainUSMicrosoft PowerpointSkill DevelopmentInternalPaul13-Mar-1215-Mar-123Online
96TE - 95Employee - 95JMSalesCanadaBusiness WritingProfessional DevelopmentExternalMartha18-Sep-1218-Sep-121Online
97TE - 96Employee - 96JMSupply ChainCanadaBusiness WritingProfessional DevelopmentOnlineMartha30-Jul-1330-Jul-131External
98TE - 97Employee - 97JMOthersCanadaSix SigmaSkill DevelopmentExternalTom15-Jul-1416-Jul-142Online
99TE - 98Employee - 98JMHuman ResourceEuropeCommunicate to ImpactProfessional DevelopmentInternalGreg28-Aug-1328-Aug-131External
100TE - 99Employee - 99LeadershipHuman ResourceUSBusiness GrammarSkill DevelopmentInternalGrace19-Mar-1221-Mar-123Internal
Data
 
Upvote 0
You're formula still works for me on that data
+Fluff New.xlsm
ABCDEFGHIJKLMNOPQ
1Emp IDParticipant NameJob BandBusiness UnitRegionTraining NameTraining TypeTrainer TypeTrainer NameStart DateEnd Date# of DaysOnline/Classroom2012MM7
2TE - 01Employee - 01MMCorporateUSAdvanced Management SkillsProfessional DevelopmentOnlineJoe09/07/201310/07/20132Online
13TE - 12Employee - 12MMSalesCanadaPath to Extraordinary ProductivityProfessional DevelopmentExternalLouise07/05/201408/05/20142Internal
14TE - 13Employee - 13MMMarketingEuropeBusiness GrammarSkill DevelopmentExternalGrace31/01/201331/01/20131Online
19TE - 18Employee - 18MMSalesAsia-PacificCommunicate to ImpactProfessional DevelopmentOnlineGreg23/09/201423/09/20141External
20TE - 19Employee - 19MMLegalEuropeCommunicate to ImpactProfessional DevelopmentInternalGreg21/08/201322/08/20132Online
21TE - 20Employee - 20MMOthersAsia-PacificBusiness WritingProfessional DevelopmentExternalMartha30/05/201230/05/20121Internal
23TE - 22Employee - 22MMHuman ResourceEuropeMicrosoft ExcelSkill DevelopmentInternalRay12/06/201413/06/20142Online
24TE - 23Employee - 23MMLegalCanadaMicrosoft ExcelSkill DevelopmentExternalRay06/12/201206/12/20121External
25TE - 24Employee - 24MMLogisticsAsia-PacificMicrosoft ExcelSkill DevelopmentInternalRay09/02/201509/02/20151External
26TE - 25Employee - 25MMLogisticsCanada7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen13/12/201313/12/20131Internal
27TE - 26Employee - 26MMMarketingAsia-PacificBusiness WritingProfessional DevelopmentOnlineMartha20/11/201222/11/20123Online
29TE - 28Employee - 28MMCorporateEurope7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen24/09/201326/09/20133External
30TE - 29Employee - 29MMHuman ResourceCanadaSix SigmaSkill DevelopmentExternalTom11/10/201311/10/20131Online
31TE - 30Employee - 30MMCorporateAsia-PacificCommunicate to ImpactProfessional DevelopmentInternalGreg30/04/201302/05/20133Internal
40TE - 39Employee - 39MMLogisticsCanadaCommunicate to ImpactProfessional DevelopmentInternalGreg25/06/201426/06/20142Online
53TE - 52Employee - 52MMSupply ChainAsia-PacificBusiness WritingProfessional DevelopmentExternalMartha28/11/201329/11/20132External
54TE - 53Employee - 53MMLogisticsAsia-PacificCreativity and InnovationProfessional DevelopmentExternalCharles01/04/201302/04/20132Internal
76TE - 75Employee - 75MMOthersEuropePath to Extraordinary ProductivityProfessional DevelopmentOnlineLouise10/04/201310/04/20131Online
77TE - 76Employee - 76MMSupply ChainEuropeCreativity and InnovationProfessional DevelopmentOnlineCharles24/01/201226/01/20123Internal
78TE - 77Employee - 77MMHuman ResourceUSBusiness WritingProfessional DevelopmentExternalMartha12/05/201412/05/20141Internal
80TE - 79Employee - 79MMMarketingCanadaMicrosoft PowerpointSkill DevelopmentInternalPaul30/10/201430/10/20141External
82TE - 81Employee - 81MMHuman ResourceAsia-PacificBusiness GrammarSkill DevelopmentInternalGrace01/09/201403/09/20143External
83TE - 82Employee - 82MMLegalAsia-PacificBusiness WritingProfessional DevelopmentOnlineMartha13/06/201213/06/20121External
85TE - 84Employee - 84MMLegalAsia-PacificAdvanced Management SkillsProfessional DevelopmentOnlineJoe25/04/201225/04/20121External
87TE - 86Employee - 86MMLogisticsEuropeMicrosoft ExcelSkill DevelopmentExternalRay26/09/201426/09/20141External
95TE - 94Employee - 94MMSupply ChainUSMicrosoft PowerpointSkill DevelopmentInternalPaul13/03/201215/03/20123Online
101
Master
Cell Formulas
RangeFormula
Q1Q1=SUMPRODUCT((YEAR(HRData[Start Date])=O1)*(IF(P1="All Employees",TRUE(),HRData[Job Band]=P1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:J100Expression=AND(YEAR($J2)=$O$1,$C2=$P$1)textNO
 
Upvote 0
You can copy and paste from Excel, or you could use the XL2BB tool which you can download via the last button on the toolbar above this post.
 
Upvote 0
The OP has already done that, see post#22. ;)
 
Upvote 0
I used named ranges and put the criteria at the top.

T202009c.xlsm
ABCDEFGH
1CountCount
22013All Employees2525251-Jan-131-Jan-14
32013MM10
4
5Emp IDParticipant NameJob BandBusiness UnitRegionTraining NameTraining TypeTrainer Type
6TE - 01Employee - 01MMCorporateUSAdvanced Management SkillsProfessional DevelopmentOnline
7TE - 02Employee - 02JMCorporateAsia-PacificBusiness WritingProfessional DevelopmentOnline
1d
Cell Formulas
RangeFormula
E2E2=COUNTIF(StartDate,">="&$G$2)-COUNTIF(StartDate,">="&$H$2)
F2F2=SUMPRODUCT(--(YEAR(StartDate)=$A$2))
D2:D3D2=SUMPRODUCT(--(YEAR(StartDate)=A2),--(JobBand=B2)+(B2="All Employees"))
Named Ranges
NameRefers ToCells
JobBand='1d'!$C$6:$C$104D2:D3
StartDate='1d'!$J$6:$J$104E2:F2, D2:D3
 
Upvote 0
Thanks for the feedback.
I document ideas with 2 additional examples.
T202009c.xlsm
ABC
1YearJob BandJob Band Count
22013All Employees25
32013MM10
42013JM11
52013SM4
620130
7
1dd
Cell Formulas
RangeFormula
A3:A6A3=$A$2
C2:C6C2=SUMPRODUCT(--(YEAR(HRData[Start Date])=A2),--(HRData[Job Band]=B2)+(B2="All Employees"))


The next example uses a UDF to show the criteria and a formula to show the count.

T202009c.xlsm
ABCDEK
1Count10=MM=2013
2101010
3Emp IDParticipant NameJob BandBusiness UnitRegionYear
4TE - 01Employee - 01MMCorporateUS2013
1d
Cell Formulas
RangeFormula
B1B1=SUBTOTAL(3,A4:A102)
C1,K1C1=ShowFilter(C4:C102)
C2,K2,E2C2=SUBTOTAL(3,C4:C102)
K4K4=YEAR('1d'!$J4)
 
Upvote 0
Dave Patton said:
Thanks for the feedback.
I document ideas with 2 additional examples.
T202009c.xlsm
ABC
1YearJob BandJob Band Count
22013All Employees25
32013MM10
42013JM11
52013SM4
620130
7
1dd
Cell Formulas
RangeFormula
A3:A6A3=$A$2
C2:C6C2=SUMPRODUCT(--(YEAR(HRData[Start Date])=A2),--(HRData[Job Band]=B2)+(B2="All Employees"))


The next example uses a UDF to show the criteria and a formula to show the count.

T202009c.xlsm
ABCDEK
1Count10=MM=2013
2101010
3Emp IDParticipant NameJob BandBusiness UnitRegionYear
4TE - 01Employee - 01MMCorporateUS2013
1d
Cell Formulas
RangeFormula
B1B1=SUBTOTAL(3,A4:A102)
C1,K1C1=ShowFilter(C4:C102)
C2,K2,E2C2=SUBTOTAL(3,C4:C102)
K4K4=YEAR('1d'!$J4)
Click to expand...

Thank you Dave for the solutions.
I'll take a closer look.
 
Upvote 0
Fluff said:
You're formula still works for me on that data
+Fluff New.xlsm
ABCDEFGHIJKLMNOPQ
1Emp IDParticipant NameJob BandBusiness UnitRegionTraining NameTraining TypeTrainer TypeTrainer NameStart DateEnd Date# of DaysOnline/Classroom2012MM7
2TE - 01Employee - 01MMCorporateUSAdvanced Management SkillsProfessional DevelopmentOnlineJoe09/07/201310/07/20132Online
13TE - 12Employee - 12MMSalesCanadaPath to Extraordinary ProductivityProfessional DevelopmentExternalLouise07/05/201408/05/20142Internal
14TE - 13Employee - 13MMMarketingEuropeBusiness GrammarSkill DevelopmentExternalGrace31/01/201331/01/20131Online
19TE - 18Employee - 18MMSalesAsia-PacificCommunicate to ImpactProfessional DevelopmentOnlineGreg23/09/201423/09/20141External
20TE - 19Employee - 19MMLegalEuropeCommunicate to ImpactProfessional DevelopmentInternalGreg21/08/201322/08/20132Online
21TE - 20Employee - 20MMOthersAsia-PacificBusiness WritingProfessional DevelopmentExternalMartha30/05/201230/05/20121Internal
23TE - 22Employee - 22MMHuman ResourceEuropeMicrosoft ExcelSkill DevelopmentInternalRay12/06/201413/06/20142Online
24TE - 23Employee - 23MMLegalCanadaMicrosoft ExcelSkill DevelopmentExternalRay06/12/201206/12/20121External
25TE - 24Employee - 24MMLogisticsAsia-PacificMicrosoft ExcelSkill DevelopmentInternalRay09/02/201509/02/20151External
26TE - 25Employee - 25MMLogisticsCanada7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen13/12/201313/12/20131Internal
27TE - 26Employee - 26MMMarketingAsia-PacificBusiness WritingProfessional DevelopmentOnlineMartha20/11/201222/11/20123Online
29TE - 28Employee - 28MMCorporateEurope7 Habits of Highly Effective ExecutivesProfessional DevelopmentInternalStephen24/09/201326/09/20133External
30TE - 29Employee - 29MMHuman ResourceCanadaSix SigmaSkill DevelopmentExternalTom11/10/201311/10/20131Online
31TE - 30Employee - 30MMCorporateAsia-PacificCommunicate to ImpactProfessional DevelopmentInternalGreg30/04/201302/05/20133Internal
40TE - 39Employee - 39MMLogisticsCanadaCommunicate to ImpactProfessional DevelopmentInternalGreg25/06/201426/06/20142Online
53TE - 52Employee - 52MMSupply ChainAsia-PacificBusiness WritingProfessional DevelopmentExternalMartha28/11/201329/11/20132External
54TE - 53Employee - 53MMLogisticsAsia-PacificCreativity and InnovationProfessional DevelopmentExternalCharles01/04/201302/04/20132Internal
76TE - 75Employee - 75MMOthersEuropePath to Extraordinary ProductivityProfessional DevelopmentOnlineLouise10/04/201310/04/20131Online
77TE - 76Employee - 76MMSupply ChainEuropeCreativity and InnovationProfessional DevelopmentOnlineCharles24/01/201226/01/20123Internal
78TE - 77Employee - 77MMHuman ResourceUSBusiness WritingProfessional DevelopmentExternalMartha12/05/201412/05/20141Internal
80TE - 79Employee - 79MMMarketingCanadaMicrosoft PowerpointSkill DevelopmentInternalPaul30/10/201430/10/20141External
82TE - 81Employee - 81MMHuman ResourceAsia-PacificBusiness GrammarSkill DevelopmentInternalGrace01/09/201403/09/20143External
83TE - 82Employee - 82MMLegalAsia-PacificBusiness WritingProfessional DevelopmentOnlineMartha13/06/201213/06/20121External
85TE - 84Employee - 84MMLegalAsia-PacificAdvanced Management SkillsProfessional DevelopmentOnlineJoe25/04/201225/04/20121External
87TE - 86Employee - 86MMLogisticsEuropeMicrosoft ExcelSkill DevelopmentExternalRay26/09/201426/09/20141External
95TE - 94Employee - 94MMSupply ChainUSMicrosoft PowerpointSkill DevelopmentInternalPaul13/03/201215/03/20123Online
101
Master
Cell Formulas
RangeFormula
Q1Q1=SUMPRODUCT((YEAR(HRData[Start Date])=O1)*(IF(P1="All Employees",TRUE(),HRData[Job Band]=P1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:J100Expression=AND(YEAR($J2)=$O$1,$C2=$P$1)textNO
Click to expand...

So I decided to evaluate the formula over a very small Data set(only ten rows).
Here's the formula.

=SUMPRODUCT((YEAR(Table2[Start Date])=2012)*(IF($O$2="All Employees",TRUE(),Table2[Job Band]="MM")))
Click to expand...

Here's the evaluate formula box


I'm not sure but it looks like the IF part of the statement only evaluates once.
Shouldn't the structure of the IF part, look the same as the first criteria with equal number of Trues and Falses.
 

Attachments

  • 1601109032936.png
    1601109032936.png
    16.8 KB · Views: 4
Upvote 0
I used the sample Data that you posted previously and the formula works.
With Sumproduct, the + means or and if C2="All Employees" True evaluates to 1 and the SumProduct uses the 1.

Try the formula that I suggested.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
ActiveCell vs Active range search
Replies
3
Views
241
Joe4
Joe4
A
  • Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
155
AlfredDB
A
I
  • Question
Tracking vacation requests
Replies
9
Views
511
KRice
K
W
  • Solved
Formula mod to include AND in Logic
Replies
1
Views
245
SanjayGMusafir
SanjayGMusafir
W
  • Locked
  • Question
If Statement Help
Replies
1
Views
344
Fluff
Fluff

Forum statistics

Threads
1,215,626
Messages
6,125,896
Members
449,271
Latest member
bergy32204

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