= 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

	D​	E​	F​	G​	H​	I​
1​	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​
3​	26​	9/17/2018​	9/23/2018​	9/25/2018​	2000​	40​
4​	10​	9/17/2018​	9/23/2018​	9/25/2018​	2600​	40​
5​	30​	9/17/2018​	9/23/2018​	9/25/2018​	1600​	40​
6​	29​	9/17/2018​	9/23/2018​	9/25/2018​	1600​	40​
7​	62​	9/17/2018​	9/23/2018​	9/25/2018​	1800​	40​
8​	4​	9/17/2018​	9/23/2018​	9/25/2018​	4326.8​	40​
9​	6​	9/17/2018​	9/23/2018​	9/25/2018​
10​	6​	9/17/2018​	9/23/2018​	9/25/2018​
11​	7​	9/17/2018​	9/23/2018​	9/25/2018​
12​	7​	9/17/2018​	9/23/2018​	9/25/2018​
13​	11​	9/17/2018​	9/23/2018​	9/25/2018​
14​	11​	9/17/2018​	9/23/2018​	9/25/2018​
15​	1​	9/17/2018​	9/23/2018​	9/25/2018​	750​	20​
16​	1​	9/17/2018​	9/23/2018​	9/25/2018​	750​	20​
17​	14​	9/17/2018​	9/23/2018​	9/25/2018​
18​	14​	9/17/2018​	9/23/2018​	9/25/2018​
19​	3​	9/17/2018​	9/23/2018​	9/25/2018​
20​	3​	9/17/2018​	9/23/2018​	9/25/2018​
21​	15​	9/17/2018​	9/23/2018​	9/25/2018​	2265.2​	40​
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​
26​	5​	9/17/2018​	9/23/2018​	9/25/2018​	1070​	10​
27​	57​	9/17/2018​	9/23/2018​	9/25/2018​	2050.8​	40​

Sheet: Sheet2

 

[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