SUM cells in a column >0 based on the month and year of another column

Wellness

Wellness

New Member
Joined
Oct 20, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello all, I am currently stuck on what is proving to be a complicated formula...I fear Ive been trying to many things to where now my brain is spaghetti lol.

*NOTE* Im using excel via outlook email, I dont actually have excel on my device so I couldnt use the minisheet file. I tried, SORRY =(*

Goals:
* Get J4 to SUM Specific Cells located in Cell range G3:G999999 that are based on the Month and Year of Cells A3:A999999
Example: SUM "Net Gross Totals" of only Month & Year: November, 2023 of all the Months and Years listed in Column A

my brain is fried. PLEASE HELP.
 

Attachments

  • what_formula.png
    what_formula.png
    154.6 KB · Views: 15

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Instead of using SUMIFS, use array forumulas:
Mr excel questions 67.xlsm
ABCDEFGHIJKL
1
2Date
3Saturday, October 14, 2023246464
4Saturday, October 21, 202319
5Saturday, October 28, 202313
6Saturday, November 4, 202316
7Saturday, November 11, 202319
8Saturday, November 18, 202316
9Saturday, November 25, 202313
10Saturday, December 2, 202316
11Saturday, December 9, 202313
12Saturday, December 16, 202325
13Saturday, December 23, 202319
14Saturday, December 30, 202311
15Saturday, January 6, 202420
16Saturday, January 13, 202417
17Saturday, January 20, 202411
18Saturday, January 27, 202410
19Saturday, February 3, 202424
20Saturday, February 10, 202412
21Saturday, February 17, 202422
22Saturday, February 24, 202420
23Saturday, March 2, 202418
24Saturday, March 9, 202419
25Saturday, March 16, 202417
26Saturday, March 23, 202424
27Saturday, March 30, 202415
28Saturday, April 6, 202420
29Saturday, April 13, 202421
30Saturday, April 20, 202420
31Saturday, April 27, 202423
32Saturday, May 4, 202415
33Saturday, May 11, 202412
34Saturday, May 18, 202424
35Saturday, May 25, 202421
Sheet2
Cell Formulas
RangeFormula
J3J3=SUM(((MONTH(A3:A35)=11)*((YEAR(A3:A35)=2023)*(G3:G35))))
K3K3=SUM(FILTER(G3:G35,(MONTH(A3:A35)=11)*(YEAR(A3:A35)=2023)))
A4:A35A4=A3+7


Note: Try to limit your number of rows in calculations. Unless you really have 999999 rows it may slow your worksheet down.
 
Last edited:
Upvote 1
Solution
A slightly different approach for making a list of unique month/year combinations and determining which elements of the array to sum:
what_formula.xlsx
AGHIJ
2DateUnique mmm-yyyySum
3Saturday, October 14, 202324Oct-202356
4Saturday, October 21, 202319Nov-202364
5Saturday, October 28, 202313Dec-202384
6Saturday, November 4, 202316Jan-202458
7Saturday, November 11, 202319Feb-202478
8Saturday, November 18, 202316Mar-202493
9Saturday, November 25, 202313Apr-202484
10Saturday, December 2, 202316May-202472
11Saturday, December 9, 202313
12Saturday, December 16, 202325
13Saturday, December 23, 202319
14Saturday, December 30, 202311
15Saturday, January 6, 202420
16Saturday, January 13, 202417
17Saturday, January 20, 202411
18Saturday, January 27, 202410
19Saturday, February 3, 202424
20Saturday, February 10, 202412
21Saturday, February 17, 202422
22Saturday, February 24, 202420
23Saturday, March 2, 202418
24Saturday, March 9, 202419
25Saturday, March 16, 202417
26Saturday, March 23, 202424
27Saturday, March 30, 202415
28Saturday, April 6, 202420
29Saturday, April 13, 202421
30Saturday, April 20, 202420
31Saturday, April 27, 202423
32Saturday, May 4, 202415
33Saturday, May 11, 202412
34Saturday, May 18, 202424
35Saturday, May 25, 202421
Sheet2
Cell Formulas
RangeFormula
I3:I10I3=UNIQUE(EOMONTH(+A3:A35,0))
J3:J10J3=SUM(FILTER($G$3:$G$35,EOMONTH(+$A$3:$A$35,0)=I3))
A4:A35A4=A3+7
Dynamic array formulas.
 
Upvote 1
Another way with Power query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Year", type text}}, "en-US"),{"Month Name", "Year"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"DATE.1"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"DATE.1"}, {{"Total", each List.Sum([Column6]), type number}})
in
    #"Grouped Rows"
 
Upvote 1
Everyone here who replied THANK YOU and I love you all for your help.

Mr. awoohaw was the first message I read and it was the first solution I tried and it worked like a charm! Thank you sir and sir's! 🥂
awoohaw said:
Instead of using SUMIFS, use array forumulas:
Mr excel questions 67.xlsm
ABCDEFGHIJKL
1
2Date
3Saturday, October 14, 2023246464
4Saturday, October 21, 202319
5Saturday, October 28, 202313
6Saturday, November 4, 202316
7Saturday, November 11, 202319
8Saturday, November 18, 202316
9Saturday, November 25, 202313
10Saturday, December 2, 202316
11Saturday, December 9, 202313
12Saturday, December 16, 202325
13Saturday, December 23, 202319
14Saturday, December 30, 202311
15Saturday, January 6, 202420
16Saturday, January 13, 202417
17Saturday, January 20, 202411
18Saturday, January 27, 202410
19Saturday, February 3, 202424
20Saturday, February 10, 202412
21Saturday, February 17, 202422
22Saturday, February 24, 202420
23Saturday, March 2, 202418
24Saturday, March 9, 202419
25Saturday, March 16, 202417
26Saturday, March 23, 202424
27Saturday, March 30, 202415
28Saturday, April 6, 202420
29Saturday, April 13, 202421
30Saturday, April 20, 202420
31Saturday, April 27, 202423
32Saturday, May 4, 202415
33Saturday, May 11, 202412
34Saturday, May 18, 202424
35Saturday, May 25, 202421
Sheet2
Cell Formulas
RangeFormula
J3J3=SUM(((MONTH(A3:A35)=11)*((YEAR(A3:A35)=2023)*(G3:G35))))
K3K3=SUM(FILTER(G3:G35,(MONTH(A3:A35)=11)*(YEAR(A3:A35)=2023)))
A4:A35A4=A3+7


Note: Try to limit your number of rows in calculations. Unless you really have 999999 rows it may slow your worksheet down.
Click to expand...
 
Upvote 0
Thanks for the feedback. Welcome to the Mr. Excel forum!

Best Wishes!
 
Upvote 1
You must log in or register to reply here.

Similar threads

itsgrady
  • Solved
Update to sum from year and month
Replies
3
Views
215
felixstraube
F
Y
  • Question
How to have a formula auto update based off the number of filled rows.
Replies
0
Views
204
Yaeger38
Y
B
  • Question
Excel 2021 - Sum cells that contain text for total and total by month
Replies
1
Views
72
bebo021999
B
S
  • Question
Cell values in same column summed to different cells based on color
Replies
3
Views
74
dermie_72
D
J
  • Question
Sum data in a range based on a column and row and an equation
Replies
1
Views
142
bobaol
B

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

Share this page

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