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.

Thanks for your help

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

A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||

1 | Task No | Worker | Week | Concept | Duration | Status | Deadline | Confirmed Finish Date | ||||||||||||||||||||

2 | 5532G21 | John | 1.week | Correction | 20 | OK | 21.10.20 | 22.10.20 | Question 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 | |||||||||||||||||||

3 | 902321R | John | 1.week | Second work | 20 | OK | 23.10.20 | 24.10.20 | Formula 1 | Countifs? | ||||||||||||||||||

4 | 5536G21 | John | 1.week | New Work | 20 | OK | 21.10.20 | 23.10.20 | Formula 2 | 4 | 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"))) | |||||||||||||||||

5 | 5532G22 | Karhan | 2.week | MK | 20 | Processed | 23.10.20 | 23.10.20 | ||||||||||||||||||||

6 | 5432F21 | John | 1.week | New Work | 12 | Processed | 22.10.20 | Quetion 2-) Find the number of delayed tasks depend on selected worker (John) | ||||||||||||||||||||

7 | 548222D | Kylee | 2.week | SK | 12 | Processed | 28.10.20 | Formula 1 | 3 | SUMPRODUCT((H2:H30>G2:G30)*(B2:B30="John")) | ||||||||||||||||||

8 | 5593AB1 | Tomas | 6.week | SK | 20 | OK | 10.10.20 | 10.10.20 | Formula 2 | Countifs? | ||||||||||||||||||

9 | 908751Z | John | 2.week | SK | 12 | Waiting | 20.10.20 | |||||||||||||||||||||

10 | 5532ABC | Mattheus | 3.week | SK | 12 | OK | 22.10.20 | 21.10.20 | Question 3-) Find the duration of delay for delayed tasks of John. | |||||||||||||||||||

11 | 5532D09 | Dennis | 3.week | SK | 12 | Processed | 02.11.20 | 03.11.20 | Formula 1 | 4 | SUMPRODUCT((H2:H30-G2:G30)*(B2:B30="John")*(H2:H30<>"")) | |||||||||||||||||

12 | 90RFG67 | Markus | 3.week | SK | 20 | OK | 21.10.20 | 21.10.20 | Formula 2 | Sumifs? | ||||||||||||||||||

13 | 54839GE | Tomas | 4.week | New Work | 14 | OK | 23.10.20 | 21.10.20 | ||||||||||||||||||||

14 | 5532G21 | Karhan | 4.week | New Work | 2 | OK | 21.10.20 | 21.10.20 | Question 4-) Sum the duration of tasks between specific two dates | |||||||||||||||||||

15 | 902321R | Mattheus | 4.week | New Work | 4 | Processed | 23.10.20 | 24.10.20 | Formula 1 | 88 | SUMIFS(E:E;H:H;">="&TARİH(2020;10;21);H:H;"<="&TARİH(2020;10;22)) | |||||||||||||||||

16 | 5536G21 | Karhan | 4.week | New Work | 2 | Processed | 22.10.20 | 23.10.20 | Formula 2 | 88 | SUMIFS(E:E;H:H;">=21.10.2020";H:H;"<22.10.2020") | |||||||||||||||||

17 | 5532G22 | Karhan | 5.week | Correction | 14 | Processed | 28.10.20 | Formula 3 | SUMPRODUCT? | |||||||||||||||||||

18 | 9032F21 | John | 5.week | Correction | 5 | OK | 10.10.20 | |||||||||||||||||||||

19 | 548222D | Kylee | 5.week | Correction | 3 | Waiting | 20.10.20 | |||||||||||||||||||||

20 | 5593AB1 | Kylee | 5.week | Correction | 6 | OK | 22.10.20 | 21.10.20 | ||||||||||||||||||||

21 | 908751Z | Kylee | 5.week | New Work | 2 | Processed | 02.11.20 | 03.11.20 | ||||||||||||||||||||

22 | 5532ABC | Jonnathan | 5.week | New Work | 0 | OK | 21.10.20 | 23.10.20 | ||||||||||||||||||||

23 | 908751Z | Daniel | 5.week | Second work | 22 | OK | 23.10.20 | |||||||||||||||||||||

24 | 5532ABC | John | 4.week | Correction | 3 | OK | 21.10.20 | |||||||||||||||||||||

25 | 5532D09 | John | 6.week | Second work | 15 | Processed | 23.10.20 | |||||||||||||||||||||

26 | 90RFG67 | Mattheus | 6.week | New Work | 18 | Processed | 22.10.20 | 10.10.20 | ||||||||||||||||||||

27 | 54839GE | Kylee | 7.week | New Work | 10 | Processed | 28.10.20 | |||||||||||||||||||||

28 | 90RFG67 | Dennis | 7.week | Second work | 14 | OK | 10.10.20 | 21.10.20 | ||||||||||||||||||||

29 | 54839GE | Solomon | 7.week | New Work | 14 | Waiting | 20.10.20 | |||||||||||||||||||||

30 | 54839GE | Jonnathan | 7.week | New Work | 14 | OK | 22.10.20 | |||||||||||||||||||||

31 | ||||||||||||||||||||||||||||

Sheet1 |

Cell Formulas | ||
---|---|---|

Range | Formula | |

L4 | L4 | =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"))) |

L7 | L7 | =SUMPRODUCT((H2:H30>G2:G30)*(B2:B30="John")) |

L11 | L11 | =SUMPRODUCT((H2:H30-G2:G30)*(B2:B30="John")*(H2:H30<>"")) |

L15 | L15 | =SUMIFS(E:E,H:H,">="&DATE(2020,10,21),H:H,"<="&DATE(2020,10,22)) |

L16 | L16 | =SUMIFS(E:E,H:H,">=21.10.2020",H:H,"<=22.10.2020") |

Thanks for your help

Last edited: