ExcelBanter - Sumif between 2 numbers

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Sumif between 2 numbers (https://www.excelbanter.com/excel-worksheet-functions/270434-sumif-between-2-numbers.html)

Sumif between 2 numbers

I am trying to create a sumif which will add numbers which are between two ranges. Here is the data:

On Time 160
1 day late 4
2 days late 3
3 days late 2
4 days late 3
6 days late 5
7 days late 2
8 days late 1
10 days late 2
11 days late 1
12 days late 4
13 days late 2
14 days late 6
15 days late 1
16 days late 2
17 days late 1
20 days late 2
21 days late 3
22 days late 1
26 days late 1
28 days late 1
29 days late 2
30 days late 1
31 days late 1
34 days late 1
45 days late 1
65 days late 1

I would like to break the infor down into weeks, so in week one I would have 19 late.

Any thoughts?

Quote:

Originally Posted by Randy Mis (Post 964951)

I am trying to create a sumif which will add numbers which are between two ranges.

This is easily achieved using the SUMIFS function, which allows you to enter multiple criteria. The following shows the results I received using this in a formula, however I pared back your data to the raw info needed for the calculation:

Code:

 A      B      C      D

Day  Late    Week  Total

 1      4      1    19

 2      3      2    16

 3      2      3      9

 4      3      4      3

 6      5      5      5

 7      2      6      0

 8      1      7      1

10      2      8      0

11      1      9      0

12      4      10    1

13      2

14      6

15      1

16      2

17      1

20      2

21      3

22      1

26      1

28      1

29      2

30      1

31      1

34      1

45      1

65      1

The formula in cell D2 to return the value of 19 is:

Code:

=SUMIFS($B$2:$B$28,$A$2:$A$28,"=" & C2*7-6,$A$2:$A$28,"<=" & C2*7)

...copy this formula down for each week and it will sum the late column based on the week.