= sumifs ?

[zone709]

Hi so this works below and I locked the other cells because I need to drag this down like 200 lines. The only thing as of now I cant get to change is the 2 to go to 3 then 4 and so on. Anyway of doing this so I don't have to go into each cell and do it.


=SUMIFS($I$2:$I$1000,$D$2:$D$1000,"2")

Example when I drag it down I need it to change like this.

=SUMIFS($I$2:$I$1000,$D$2:$D$1000,"3")

=SUMIFS($I$2:$I$1000,$D$2:$D$1000,"4")

and on​

_{^{<strike>
</strike>}}​

 

[hrayani]

My friend. Surely it will look the values in Column D. But the question is what value to look at?
so it will match the cell value of J2 (i.e. 2) in column D

just type 2 in cell J2 put this formula in J3. (=J2+1) and drag it down to the desired range.
then enter the formula I provided in post #6 and drag it down too - to the desired range.

 

[hrayani]

Sure u can put the formula in AK2. Doesn’t matter

 

[zone709]

sorry just confusing at the moment and cant get it. The value to add up is in column I the numbers to go off of are from column D

 

[r1998]

Hello Zone790
Is this the answer that u want

ID	PERIOD START DATE	PERIOD END DATE	CHECK DATE	HOURLY AMOUNT	HOURLY HOURS
2	39	9/17/2018	9/23/2018	9/25/2018	1800	40	1800
3	26	9/17/2018	9/23/2018	9/25/2018	2000	40	3800
4	10	9/17/2018	9/23/2018	9/25/2018	2600	40	6400
5	30	9/17/2018	9/23/2018	9/25/2018	1600	40	8000
6	29	9/17/2018	9/23/2018	9/25/2018	1600	40	9600
7	62	9/17/2018	9/23/2018	9/25/2018	1800	40	11400
8	4	9/17/2018	9/23/2018	9/25/2018	4326.8	40	15726.8
9	6	9/17/2018	9/23/2018	9/25/2018			15726.8
10	6	9/17/2018	9/23/2018	9/25/2018			15726.8
11	7	9/17/2018	9/23/2018	9/25/2018			15726.8
12	7	9/17/2018	9/23/2018	9/25/2018			15726.8
13	11	9/17/2018	9/23/2018	9/25/2018			15726.8
14	11	9/17/2018	9/23/2018	9/25/2018			15726.8
15	1	9/17/2018	9/23/2018	9/25/2018	750	20	16476.8
16	1	9/17/2018	9/23/2018	9/25/2018	750	20	17226.8
17	14	9/17/2018	9/23/2018	9/25/2018			17226.8
18	14	9/17/2018	9/23/2018	9/25/2018			17226.8
19	3	9/17/2018	9/23/2018	9/25/2018			17226.8
20	3	9/17/2018	9/23/2018	9/25/2018			17226.8
21	15	9/17/2018	9/23/2018	9/25/2018	2265.2	40	19492
22	9	9/17/2018	9/23/2018	9/25/2018			19492
23	9	9/17/2018	9/23/2018	9/25/2018			19492
24	5	9/17/2018	9/23/2018	9/25/2018			19492
25	5	9/17/2018	9/23/2018	9/25/2018	3210	30	22702
26	5	9/17/2018	9/23/2018	9/25/2018	1070	10	23772
27	57	9/17/2018	9/23/2018	9/25/2018	2050.8	40	25822.8

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

In k2 i used this formula

=I2

in k3 i used this formula n dragged it down till k27

=I3+K2

 

[Fluff]

How about
=SUMIFS($I$2:$I$1000,$D$2:$D$1000,ROW(D2))

 

[zone709]

First column below is lets say D look at the number 5 in D right sums up whatever is in I 30 + 10 returns 40 9 has 0 returns nothing

Excel 2016 (Windows) 32 bit

	D​	E​	F​	G​	H​	I​	J​
22​	9​	9/17/2018​	9/23/2018​	9/25/2018​
23​	9​	9/17/2018​	9/23/2018​	9/25/2018​
24​	5​	9/17/2018​	9/23/2018​	9/25/2018​
25​	5​	9/17/2018​	9/23/2018​	9/25/2018​	3210​	30​	107​
26​	5​	9/17/2018​	9/23/2018​	9/25/2018​	1070​	10​	107​

Sheet: Sheet2

 

[zone709]

Fluff thats it works thanks so much. Thanks guys also for the help. Will use fluff's for now it works.

 

[Fluff]

Glad we could help & thanks for the feedback