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

Well u can give a cell reference instead of 2 and drag it down.

 

[zone709]

hi you have an example ? I have 2 or 3 and on because it needs to find those number to sum the columns up in I

 

[hrayani]

For example type 2 in cell J2 and 3 in Cell J3 and drag it down to the desired range
after that replace 2 with cell reference. In this case it’s J2. Don’t lock it and drag it down

i am sure an expert could provide a much better solution.

 

[zone709]

confused sorry

 

[hrayani]

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

drag it it down to the desired range

 

[zone709]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]ID[/td][td]PERIOD START DATE[/td][td]PERIOD END DATE[/td][td]CHECK DATE[/td][td]HOURLY AMOUNT[/td][td]HOURLY HOURS[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

39​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

1800​

[/td][td]

40​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

26​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

2000​

[/td][td]

40​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

10​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

2600​

[/td][td]

40​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

30​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

1600​

[/td][td]

40​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

29​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

1600​

[/td][td]

40​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

62​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

1800​

[/td][td]

40​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

4​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

4326.8​

[/td][td]

40​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

6​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

6​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

7​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]

7​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]

11​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]

11​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]

1​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

750​

[/td][td]

20​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]

1​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

750​

[/td][td]

20​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]

14​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td]

14​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

19​

[/td][td]

3​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

20​

[/td][td]

3​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

21​

[/td][td]

15​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

2265.2​

[/td][td]

40​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

22​

[/td][td]

9​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

23​

[/td][td]

9​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

24​

[/td][td]

5​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

25​

[/td][td]

5​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

3210​

[/td][td]

30​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

26​

[/td][td]

5​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

1070​

[/td][td]

10​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

27​

[/td][td]

57​

[/td][td]

9/17/2018​

[/td][td]

9/23/2018​

[/td][td]

9/25/2018​

[/td][td]

2050.8​

[/td][td]

40​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[hrayani]

See post # 6
that should do

 

[zone709]

why J2? Its looking for the number in D 2 3 4 etc

 

[zone709]

o if you saying to put formula in J thats why you said J2. I am putting my formula in AK, but when I put that formula in AK2 I get 0. Even if i put it in J right it returns 0