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.

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

<tbody>
</tbody>

 

[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

		Current Year	2019
		Current Period	3
		Actual	Budget	Variance	Actual	Budget	Variance	Actual	Budget	Variance	Actual	Budget	Variance
Natural	Depart	1	1	1	2	2	2	3	3	3	YTD	YTD	YTD
50000	010	100,000	105,933	(439)	100,000	105,933	(105,933)	150,000	105,933	(105,933)	150,000	-	-

<colgroup><col span="2"><col><col span="2"><col><col span="2"><col><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

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