# Creating formulas with countifs/sumifs/sumproduct

#### ODSCm

##### New Member
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
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")

Last edited:

#### Fluff

##### MrExcel MVP, Moderator
I'm confused...
All the values are returned by a single formula in one cell, it's not a formula that has been dragged down.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### ODSCm

##### New Member
Sorry...i'm wrong
By chance my formula generated the correct result
Shame

M.
Yes I also now realized. With other workers I tested. It gives wrong value. I think there is no way to write it with countif

#### Marcelo Branco

##### MrExcel MVP
All the values are returned by a single formula in one cell, it's not a formula that has been dragged down.

You're right. I didn't test the formula properly

M.

#### Marcelo Branco

##### MrExcel MVP
I think there is no way to write it with countif

Yes, it doesn't seem possible. I tried different formulas, for no avail.

M.

Replies
6
Views
165
Replies
14
Views
327
Replies
1
Views
279
Replies
3
Views
790
Replies
14
Views
1K

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.

1,151,863
Messages
5,766,814
Members
425,379
Latest member
thedoctor00

### 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.

### Which adblocker are you using?

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

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