How to calcuate YTD Sum with Quarterly/Half Inserts

[FinBusPtnr]

Hi there -

I am working on a file where expenses are listed by month. However,within that range, there are quarterly and half sums. So the range looks like:

J, F, M, Q1, A, M, J, Q2, H1, J,A, S, Q3, O, N, D, Q4, H2, FY

Looking for a way to calculate year to date expenses, excluding the quarterly and half sums. So how would I calculate April YTD spend, excluding the Q1 sum?

I would prefer a formula, but not completely opposed to VBA.

Thanks in advance!

 

[DRSteele]

Welcome to the forum.

There are many formulas to use here. These rely on expanding ranges and work differently depending upon what kind of heading you have for the month. They eliminate the Qs, the Hs and the FY from the summations in each column.

Copy the three formulas in B7 through B9 across all columns.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	Month,1	1	2	3	Q1	4	5	6	Q2	H1	7	8	9	Q3	10	11	12	Q4	H2	FY
3	Month,2	J	F	M	Q1	A	M	J	Q2	H1	J	A	S	Q3	O	N	D	Q4	H2	FY
4	Month,3	Jan	Feb	Mar	Q1	Apr	May	Jun	Q2	H1	Jul	Aug	Sep	Q3	Oct	Nov	Dec	Q4	H2	FY
5	Expenditure	92	78	93	263	92	78	93	263	526	76	35	52	163	97	46	23	166	329	855
6
7	YTD per month, 1	92	170	263	263	355	433	526	526	526	602	637	689	689	786	832	855	855	855	855
8	YTD per month, 2	92	170	263	263	355	433	526	526	526	602	637	689	689	786	832	855	855	855	855
9	YTD per month, 3	92	170	263	263	355	433	526	526	526	602	637	689	689	786	832	855	855	855	855
Sheet33

Cell Formulas
Range	Formula
B7	=SUM(IF(ISNUMBER($B$2:B2),$B$5:B5))
B8	=SUM(IF(LEN($B$3:B3)<2,$B$5:B5))
B9	=SUM(IF(LEN($B$4:B4)>2,$B$5:B5))

[/FONT]