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: 22

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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

M
  • Solved
SUM OF SALES REVENUE BASED ON SALESPERSON AND MONTH OF THE YEAR
Replies
4
Views
397
meppwc
M
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
162
Cubist
Cubist
M
  • Question Question
How to sum cells in a column that match an End of Month date in an adjacent column
Replies
7
Views
261
Peter_SSs
Peter_SSs
T
  • Question Question
I need an excel formula to not update a cell if the date is past the last day of that month
Replies
5
Views
71
Joe4
Joe4
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
192
Abdo
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

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