Find sum by month

Frozen00

New Member
Joined
Mar 12, 2012
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I am looking to generate a table from a much larger dataset where it sums up planned amounts vs actual amounts by month. Below is a notional idea of the date table, and the table I want the results formated. In the read data have the dataset within a named dynamic range that grows as data is added. Looking to do this with formulas only, no VBA.

Book2
BCDEFGHIJ
1This is a named range (MyRange)
2ProjectFunded AmountPlannedActualMonthPlannedActual
3Apples5005/6/20234/18/20231/31/2023
4Apples4006/6/20235/25/20232/28/2023
5Grapes3007/8/20236/15/20233/31/2023
6Apples507/9/20237/8/20234/30/2023
7Oranges508/6/20239/15/20235/31/2023
8Pinapple10009/15/20239/10/20236/30/2023
9Apples8001/5/20242/1/20247/31/2023
10Apples6001/5/20242/16/20248/31/2023
11Apples4002/1/20241/25/20249/30/2023
12Grapes7003/1/20243/1/202410/31/2023
13Oranges5006/1/20245/1/202411/30/2023
14Pinapple9006/1/20245/28/202412/31/2023
151/31/2024
162/29/2024
173/31/2024
184/30/2024
195/31/2024
206/30/2024
Sheet1
Cell Formulas
RangeFormula
H4:H20H4=EOMONTH(H3,1)
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about
Excel Formula:
=SUMIFS($C:$C,D:D,"<="&$H3,D:D,">"&EOMONTH($H3,-1))
 
Upvote 0
Oh and I forgot, I need to be able to exclude items that fall into a "project", so for the example above, assume I need to exclude "Pinapple" from the summed values.
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about
Excel Formula:
=SUMIFS($C:$C,D:D,"<="&$H3,D:D,">"&EOMONTH($H3,-1))
using 365, so the latest and "greatest" haha
 
Upvote 0
You can exclude pineapple like
Excel Formula:
=SUMIFS($C:$C,D:D,"<="&$H3,D:D,">"&EOMONTH($H3,-1),$B:$B,"<>Pineapple")
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

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