sumifs & index & match

[BeckyLH88]

Hello everyone,

I've been staring at this for a long while now. I have been using this formula:

=SUMIFS(INDEX(Budget!$E$4:$P$41,0,MATCH($B$4,Budget!$E$1:$P$1,0)),Budget!$B$4:$B$41,$C7)

Where B4 = the date.

E4 to P41 = the data range to sum
E1 to P1 = dates by end of month date
B4 to B41 = account code

This formula is fine, if I am just looking at the current month; but I cannot work out in this formula how I can include prior months to give me a YTD total, for example July & August. At the moment this formula just gives me August.

I appreciate any help please :D

Many thanks!

 

[Fluff]

Are all the dates the 1st of the month?

 

[BeckyLH88]

Are all the dates the 1st of the month?

They are the last day of each month

 

[Fluff]

Ok, how about

=SUM(FILTER(FILTER(Budget!$E$4:$P$41,Budget!$B$4:$B$41=$C7),(Budget!$E$1:$P$1=$B$4)+(Budget!$E$1:$P$1=EOMONTH($B$4,-1))))

 

[BeckyLH88]

Ok, how about

=SUM(FILTER(FILTER(Budget!$E$4:$P$41,Budget!$B$4:$B$41=$C7),(Budget!$E$1:$P$1=$B$4)+(Budget!$E$1:$P$1=EOMONTH($B$4,-1))))

This works for Up to August, but then only sums two months when I change to September missing July. I'm not familiar with this type formula

 

[BeckyLH88]

When I copy this formula over to replicate, I need to add in another criteria for department also... getting more complicated by the second!

 

[Fluff]

Is July in E1?

 

[BeckyLH88]

Is July in E1?

Sorry for the delay in responding!
July is in E2 on the Budget tab and on the main tab I'm inputting the formula into B4

 

[Fluff]

Ok, how about

=SUM(FILTER(Budget!$E$4:INDEX(Budget!$E$41:$P$41,0,MATCH($B$4,Budget!$E$1:$P$1,0)),Budget!$B$4:$B$41=$C7))

 

[BeckyLH88]

Perfect thats great works for my summary!!! Thank you so much!

My second complication is.... I need to add a sum by department. on budget tab, the department is listed on col D and the input tabs cell B4