Trying to sum YTD data using couple different criteria

[rnieman]

Looking to sum Actual, Budget, Variance YTD data in last three columns in the correct column based on the number in current period, tried sumifs, sum/index, and sumproduct didn’t work.

[TABLE="width: 0, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Current Year
[/TD]
[TD]2019
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Current Period
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Actual
[/TD]
[TD]Budget
[/TD]
[TD]Variance
[/TD]
[TD]Actual
[/TD]
[TD]Budget
[/TD]
[TD]Variance
[/TD]
[TD]Actual
[/TD]
[TD]Budget
[/TD]
[TD]Variance
[/TD]
[TD]Actual
[/TD]
[TD]Budget
[/TD]
[TD]Variance
[/TD]
[/TR]
[TR]
[TD]Natural
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]YTD
[/TD]
[TD]YTD
[/TD]
[TD]YTD
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 105,494
[/TD]
[TD] 105,933
[/TD]
[TD] (439)
[/TD]
[TD] -
[/TD]
[TD] 105,933
[/TD]
[TD] (105,933)
[/TD]
[TD] -
[/TD]
[TD] 105,933
[/TD]
[TD] (105,933)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 30,518
[/TD]
[TD] 30,002
[/TD]
[TD] 516
[/TD]
[TD] 100,000
[/TD]
[TD] 30,002
[/TD]
[TD] 69,998
[/TD]
[TD] 800,000
[/TD]
[TD] 30,002
[/TD]
[TD] (30,002)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 83,713
[/TD]
[TD] 83,627
[/TD]
[TD] 86
[/TD]
[TD] 50,000
[/TD]
[TD] 83,627
[/TD]
[TD] (83,627)
[/TD]
[TD] -
[/TD]
[TD] 94,877
[/TD]
[TD] (94,877)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 69,269
[/TD]
[TD] 63,969
[/TD]
[TD] 5,300
[/TD]
[TD] -
[/TD]
[TD] 63,969
[/TD]
[TD] (63,969)
[/TD]
[TD] -
[/TD]
[TD] 80,635
[/TD]
[TD] (80,635)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 100,504
[/TD]
[TD] 98,454
[/TD]
[TD] 2,050
[/TD]
[TD] -
[/TD]
[TD] 98,454
[/TD]
[TD] (98,454)
[/TD]
[TD] -
[/TD]
[TD] 98,454
[/TD]
[TD] (98,454)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 36,621
[/TD]
[TD] 30,167
[/TD]
[TD] 6,455
[/TD]
[TD] -
[/TD]
[TD] 30,167
[/TD]
[TD] (30,167)
[/TD]
[TD] -
[/TD]
[TD] 42,667
[/TD]
[TD] (42,667)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 31,158
[/TD]
[TD] 27,032
[/TD]
[TD] 4,126
[/TD]
[TD] -
[/TD]
[TD] 27,032
[/TD]
[TD] (27,032)
[/TD]
[TD] -
[/TD]
[TD] 35,365
[/TD]
[TD] (35,365)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 215,391
[/TD]
[TD] 211,401
[/TD]
[TD] 3,990
[/TD]
[TD] -
[/TD]
[TD] 211,401
[/TD]
[TD] (211,401)
[/TD]
[TD] -
[/TD]
[TD] 211,401
[/TD]
[TD] (211,401)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 152,361
[/TD]
[TD] 157,216
[/TD]
[TD] (4,855)
[/TD]
[TD] -
[/TD]
[TD] 157,216
[/TD]
[TD] (157,216)
[/TD]
[TD] -
[/TD]
[TD] 157,216
[/TD]
[TD] (157,216)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 47,501
[/TD]
[TD] 46,151
[/TD]
[TD] 1,350
[/TD]
[TD] -
[/TD]
[TD] 46,151
[/TD]
[TD] (46,151)
[/TD]
[TD] -
[/TD]
[TD] 46,151
[/TD]
[TD] (46,151)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 73,030
[/TD]
[TD] 72,519
[/TD]
[TD] 511
[/TD]
[TD] -
[/TD]
[TD] 72,519
[/TD]
[TD] (72,519)
[/TD]
[TD] -
[/TD]
[TD] 72,519
[/TD]
[TD] (72,519)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 79,732
[/TD]
[TD] 84,002
[/TD]
[TD] (4,270)
[/TD]
[TD] -
[/TD]
[TD] 87,169
[/TD]
[TD] (87,169)
[/TD]
[TD] -
[/TD]
[TD] 91,336
[/TD]
[TD] (91,336)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD][/TD]
[TD] 66,294
[/TD]
[TD] 65,831
[/TD]
[TD] 463
[/TD]
[TD] -
[/TD]
[TD] 65,831
[/TD]
[TD] (65,831)
[/TD]
[TD] -
[/TD]
[TD] 65,831
[/TD]
[TD] (65,831)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] 1,091,587
[/TD]
[TD] 1,076,304
[/TD]
[TD] 15,283
[/TD]
[TD] 150,000
[/TD]
[TD] 1,079,471
[/TD]
[TD] (979,471)
[/TD]
[TD] 800,000
[/TD]
[TD] 1,132,387
[/TD]
[TD] (1,132,387)
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[TD] -
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

maybe

=SUMIFS(range row of data to add from first actual to last variance,
header [Actual/Budget/Variance] range row from first actual to last varianace, header on RHS for column to add,
period [1,1,1,2,2,2,3,3,3] range row, "< ="& current period)

with appropriate fixed references so you can drag the formula. Depending on your used cells,
=SUMIFS($C5:$K5,$C$3:$K$3,L$3,$C$4:$K$4,"< ="&$C$2)

PS
NOTE : I inserted an unwanted space in the inequality so that the formula would paste OK into the browser. The space needs to be removed for use in a spreadsheet.

 

[rnieman]

Thank you. I tried it but and it work for on period. I need it to sum all

For example if I change the "current period to 3 I need it to sum the "actual" cells for all 3 periods. So for below it would need to return 350,000 in the Actual YTD cell

[TABLE="width: 1022"]
<colgroup><col span="2"><col><col span="2"><col><col span="2"><col><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Current Year[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Current Period[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Variance[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Variance[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Variance[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]Natural[/TD]
[TD]Depart[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]YTD[/TD]
[TD]YTD[/TD]
[TD]YTD[/TD]
[/TR]
[TR]
[TD="align: right"]50000[/TD]
[TD]010[/TD]
[TD] 100,000[/TD]
[TD] 105,933[/TD]
[TD] (439)[/TD]
[TD] 100,000[/TD]
[TD] 105,933[/TD]
[TD] (105,933)[/TD]
[TD] 150,000[/TD]
[TD] 105,933[/TD]
[TD] (105,933)[/TD]
[TD] 150,000[/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
</tbody>[/TABLE]


Thank you,

Russ

 

[jim may]

I missed the below comment, at first, but after 5 minutes of trying to get results OTHER THAN 0 (zero) I was able to. Yeah!!! Tks Fazza - Jim
PS
NOTE : I inserted an unwanted space in the inequality so that the formula would paste OK into the browser. The space needs to be removed for use in a spreadsheet.d.c.

 

[jim may]

Are you using the formula furnished by Fazza? It works perfectly - whether you enter 1, 2, or 3 in the Current Period Field.
Be sure to remove the " " (space) in position 3 Below

"< ="&$C$2

so it reads:

"<="&$C$2​

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

 

[rnieman]

Yes I was using Fazza's formula but I removed a didn't remove the space just tried it with the space removed and it work's great. Thank you Fazza and Jim!

Russ