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:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,435
Office Version
  1. 365
Platform
  1. Windows
When posting data using the XL2BB add-in, you need to select all the relevant cells, not just one.
 

ODSCm

New Member
Joined
Dec 18, 2020
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
When posting data using the XL2BB add-in, you need to select all the relevant cells, not just one.
I just realized the mistake after uploading it but now it should be okey.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,435
Office Version
  1. 365
Platform
  1. Windows
Ok, for the last one you can use
Excel Formula:
=SUMPRODUCT((H2:H30>=DATE(2020,10,21))*(H2:H30<=DATE(2020,10,22)),E2:E30)
For the other 3, leave them as they are.
 

ODSCm

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

ADVERTISEMENT

Ok, for the last one you can use
Excel Formula:
=SUMPRODUCT((H2:H30>=DATE(2020,10,21))*(H2:H30<=DATE(2020,10,22)),E2:E30)
For the other 3, leave them as they are.
Thanks Fluff.
For other three isnt there a way to write also with indicated formulas?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,435
Office Version
  1. 365
Platform
  1. Windows
For Question
1) You need 3 different arrays, which you cannot do with countifs.
2) Using something like H2:H30>G2:G30 in countifs will treat it as an array, rather than a row by row comparison, so you will end up with an answer of 54.
3) Sumifs requires a range for the sum, & H2:H30-G2:G30 will create an array, so it won't work, although you could try to do two sumifs & subtract one from the other.
 

Marcelo Branco

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

ADVERTISEMENT

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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,002
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Test - observe column C

Pasta1
ABCDEFGHIJKL
1Task NoWorkerWeekConceptDurationStatusDeadlineConfirmed Finish Date
25532G21John1Correction20OK21/10/202022/10/2020Question 1
3902321RJohn1Second work20OK23/10/202024/10/2020Formula 14
45536G21John1New Work20OK21/10/202023/10/2020Formula 24
55532G22Karhan2MK20Processed23/10/202023/10/2020
65432F21John1New Work12Processed22/10/2020 Question 2
7548222DKylee2SK12Processed28/10/2020 Formula 33
85593AB1Tomas6SK20OK10/10/202010/10/2020 Formula 43
9908751ZJohn2SK12Waiting20/10/2020 
105532ABCMattheus3SK12OK22/10/202021/10/2020 Question 3
115532D09Dennis3SK12Processed02/11/202003/11/2020 Formula 14
1290RFG67Markus3SK20OK21/10/202021/10/2020 Formula 2
1354839GETomas4New Work14OK23/10/202021/10/2020 
145532G21Karhan4New Work2OK21/10/202021/10/2020 Question 4
15902321RMattheus4New Work4Processed23/10/202024/10/2020 Formula 1
165536G21Karhan4New Work2Processed22/10/202023/10/2020 Formula 2
175532G22Karhan5Correction14Processed28/10/2020 Formula 3
189032F21John5Correction5OK10/10/2020 
19548222DKylee5Correction3Waiting20/10/2020 
205593AB1Kylee5Correction6OK22/10/202021/10/2020 
21908751ZKylee5New Work2Processed02/11/202003/11/2020 
225532ABCJonnathan5New Work0OK21/10/202023/10/2020 
23908751ZDaniel5Second work22OK23/10/2020 
245532ABCJohn4Correction3OK21/10/2020 
255532D09John6Second work15Processed23/10/2020 
2690RFG67Mattheus6New Work18Processed22/10/202010/10/2020 
2754839GEKylee7New Work10Processed28/10/2020 
2890RFG67Dennis7Second work14OK10/10/202021/10/2020 
2954839GESolomon7New Work14Waiting20/10/2020 
3054839GEJonnathan7New Work14OK22/10/2020 
Plan2
Cell Formulas
RangeFormula
L3L3=SUM(COUNTIFS(B2:B30,{"John";"Kylee"},D2:D30,{"Correction","New Work"},C2:C30,">=1",C2:C30,"<=4"))
L4L4=SUMPRODUCT((B2:B30="John")+(B2:B30="Kylee"),(D2:D30="Correction")+(D2:D30="New Work"),--(C2:C30>=1),--(C2:C30<=4))
L7L7=SUMPRODUCT(--(B2:B30="John"),--(H2:H30>G2:G30))
L8L8=COUNTIFS(B2:B30,"John",H2:H30,">"&G2:G30)
L11L11=SUMPRODUCT(--(B2:B30="John"),--(H2:H30>G2:G30),H2:H30-G2:G30)
I6:I30I6=IF(B6="John",IF(H6>G6,H6-G6,""),"")


Ignore formulas in column I (used only for testing purposes)

M.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,435
Office Version
  1. 365
Platform
  1. Windows
Hmm, obviously 365 calculates countifs with arrays differently, as I get 54 for L8 rather than 3.
 
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,152,821
Messages
5,772,464
Members
425,760
Latest member
paphon

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