Creating formulas with countifs/sumifs/sumproduct

ODSCm

New Member
Joined
Dec 18, 2020
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I would like to write sum formulas with Countifs, Sumifs and with Sumproduct.
I have added excel file here. I have written what I would like to calculate and I have written equal formula for each question.
I have indicated the formula with red color which I am asking you how to write. Thans for your help.

Excel Question.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Task NoWorkerWeekConceptDurationStatusDeadlineConfirmed Finish Date
25532G21John1.weekCorrection20OK21.10.2022.10.20Question 1-) How can I calculate the number of the tasks whose types are "Correction" and "New Work", between first and fourth week which were worked by John and Kylee
3902321RJohn1.weekSecond work20OK23.10.2024.10.20Formula 1Countifs?
45536G21John1.weekNew Work20OK21.10.2023.10.20Formula 24SUMPRODUCT(((D2:D30="Correction")+(D2:D30="New Work"))*($C$2:$C$30={"1.week";"2.week";"3.week";"4.week"})*((B2:B30="John")+(B2:B30="Kylee")))
55532G22Karhan2.weekMK20Processed23.10.2023.10.20
65432F21John1.weekNew Work12Processed22.10.20Quetion 2-) Find the number of delayed tasks depend on selected worker (John)
7548222DKylee2.weekSK12Processed28.10.20Formula 13SUMPRODUCT((H2:H30>G2:G30)*(B2:B30="John"))
85593AB1Tomas6.weekSK20OK10.10.2010.10.20Formula 2Countifs?
9908751ZJohn2.weekSK12Waiting20.10.20
105532ABCMattheus3.weekSK12OK22.10.2021.10.20Question 3-) Find the duration of delay for delayed tasks of John.
115532D09Dennis3.weekSK12Processed02.11.2003.11.20Formula 14SUMPRODUCT((H2:H30-G2:G30)*(B2:B30="John")*(H2:H30<>""))
1290RFG67Markus3.weekSK20OK21.10.2021.10.20Formula 2Sumifs?
1354839GETomas4.weekNew Work14OK23.10.2021.10.20
145532G21Karhan4.weekNew Work2OK21.10.2021.10.20Question 4-) Sum the duration of tasks between specific two dates
15902321RMattheus4.weekNew Work4Processed23.10.2024.10.20Formula 188SUMIFS(E:E;H:H;">="&TARİH(2020;10;21);H:H;"<="&TARİH(2020;10;22))
165536G21Karhan4.weekNew Work2Processed22.10.2023.10.20Formula 288SUMIFS(E:E;H:H;">=21.10.2020";H:H;"<22.10.2020")
175532G22Karhan5.weekCorrection14Processed28.10.20Formula 3SUMPRODUCT?
189032F21John5.weekCorrection5OK10.10.20
19548222DKylee5.weekCorrection3Waiting20.10.20
205593AB1Kylee5.weekCorrection6OK22.10.2021.10.20
21908751ZKylee5.weekNew Work2Processed02.11.2003.11.20
225532ABCJonnathan5.weekNew Work0OK21.10.2023.10.20
23908751ZDaniel5.weekSecond work22OK23.10.20
245532ABCJohn4.weekCorrection3OK21.10.20
255532D09John6.weekSecond work15Processed23.10.20
2690RFG67Mattheus6.weekNew Work18Processed22.10.2010.10.20
2754839GEKylee7.weekNew Work10Processed28.10.20
2890RFG67Dennis7.weekSecond work14OK10.10.2021.10.20
2954839GESolomon7.weekNew Work14Waiting20.10.20
3054839GEJonnathan7.weekNew Work14OK22.10.20
31
Sheet1
Cell Formulas
RangeFormula
L4L4=SUMPRODUCT(((D2:D30="Correction")+(D2:D30="New Work"))*($C$2:$C$30={"1.week","2.week","3.week","4.week"})*((B2:B30="John")+(B2:B30="Kylee")))
L7L7=SUMPRODUCT((H2:H30>G2:G30)*(B2:B30="John"))
L11L11=SUMPRODUCT((H2:H30-G2:G30)*(B2:B30="John")*(H2:H30<>""))
L15L15=SUMIFS(E:E,H:H,">="&DATE(2020,10,21),H:H,"<="&DATE(2020,10,22))
L16L16=SUMIFS(E:E,H:H,">=21.10.2020",H:H,"<=22.10.2020")


Thanks for your help
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
It counts every date in col G (regardless of whether it's a "John" row) that is less than a date in col H & B is John
+Fluff 1.xlsm
ABCDEFGHI
1Task NoWorkerWeekConceptDurationStatusDeadlineConfirmed Finish Date
25532G21John1.weekCorrection20OK21/10/202022/10/20203
3902321RJohn1.weekSecond work20OK23/10/202024/10/20201
45536G21John1.weekNew Work20OK21/10/202023/10/20203
55532G22Karhan2.weekMK20Processed23/10/202023/10/20201
65432F21John1.weekNew Work12Processed22/10/20202
7548222DKylee2.weekSK12Processed28/10/20200
85593AB1Tomas6.weekSK20OK10/10/202010/10/20203
9908751ZJohn2.weekSK12Waiting20/10/20203
105532ABCMattheus3.weekSK12OK22/10/202021/10/20202
115532D09Dennis3.weekSK12Processed02/11/202003/11/20200
1290RFG67Markus3.weekSK20OK21/10/202021/10/20203
1354839GETomas4.weekNew Work14OK23/10/202021/10/20201
145532G21Karhan4.weekNew Work2OK21/10/202021/10/20203
15902321RMattheus4.weekNew Work4Processed23/10/202024/10/20201
165536G21Karhan4.weekNew Work2Processed22/10/202023/10/20202
175532G22Karhan5.weekCorrection14Processed28/10/20200
189032F21John5.weekCorrection5OK10/10/20203
19548222DKylee5.weekCorrection3Waiting20/10/20203
205593AB1Kylee5.weekCorrection6OK22/10/202021/10/20202
21908751ZKylee5.weekNew Work2Processed02/11/202003/11/20200
225532ABCJonnathan5.weekNew Work0OK21/10/202023/10/20203
23908751ZDaniel5.weekSecond work22OK23/10/20201
245532ABCJohn4.weekCorrection3OK21/10/20203
255532D09John6.weekSecond work15Processed23/10/20201
2690RFG67Mattheus6.weekNew Work18Processed22/10/202010/10/20202
2754839GEKylee7.weekNew Work10Processed28/10/20200
2890RFG67Dennis7.weekSecond work14OK10/10/202021/10/20203
2954839GESolomon7.weekNew Work14Waiting20/10/20203
3054839GEJonnathan7.weekNew Work14OK22/10/20202
Lists
Cell Formulas
RangeFormula
I2:I30I2=COUNTIFS(B2:B30,"John",H2:H30,">"&G2:G30)
Dynamic array formulas.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,997
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Fluff
The result in I2 seems perfect to me
=COUNTIFS(B2:B30,"John",H2:H30,">"&G2:G30)
3

I'm not understanding your problem :unsure:

M.
 

ODSCm

New Member
Joined
Dec 18, 2020
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Maybe...

EDIT: i adjusted the column C to show only numbers ( i substituted .week by """)
Using COUNTIFS
Question 1
=SUM(COUNTIFS(B2:B30,{"John";"Kylee"},D2:D30,{"Correction","New Work"},C2:C30,">=1",C2:C30,"<=4"))

Question 2
=COUNTIFS(B2:B30,"John",H2:H30,">"&G2:G30)

M.
Hello Marcelo,
Question 2 with your formula I am getting the result as "0" instead of 3.
Question 1 seems it is working
 

ODSCm

New Member
Joined
Dec 18, 2020
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Hello Marcelo,
Question 2 with your formula I am getting the result as "0" instead of 3.
Question 1 seems it is working

İt is now working I got result 3 when I made your formula as array (CTRL+shift enter). For all answers I thank Fluff and Marcelo.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@ODSCm are you trying this in xl2016 or a newer version.

The result in I2 seems perfect to me
It is, but col I is just a single formula, so if I wrap that in SUM it will add all the values together.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,997
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

İt is now working I got result 3 when I made your formula as array (CTRL+shift enter).
It worked for me with just Enter
Excel 2010

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,997
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
It is, but col I is just a single formula, so if I wrap that in SUM it will add all the values together.
I'm confused...

Why did you wrap it with SUM? AFAIK the formula doesn't require SUM

M.
 

ODSCm

New Member
Joined
Dec 18, 2020
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
@ODSCm are you trying this in xl2016 or a newer version.


It is, but col I is just a single formula, so if I wrap that in SUM it will add all the values together.
My version is MS Office professional plus 2016
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,997
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Sorry...i'm wrong
By chance my formula generated the correct result
Shame :mad:

M.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,957
Messages
5,767,308
Members
425,403
Latest member
MellieD

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
Top