sumifs & index & match

BeckyLH88

New Member
Joined
Aug 8, 2022
Messages
23
Office Version
  1. 365
Platform
  1. Web
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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Ok, how about
Excel Formula:
=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))))
 
Upvote 0
Ok, how about
Excel Formula:
=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 :)
 
Upvote 0
When I copy this formula over to replicate, I need to add in another criteria for department also... getting more complicated by the second!
 
Upvote 0
Ok, how about
Excel Formula:
=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))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,215,379
Messages
6,124,610
Members
449,174
Latest member
ExcelfromGermany

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top