sumif or sumproduct based on current month and year

A

Antispam246

New Member
Joined
Apr 4, 2015
Messages
9
2 columns, one date format xx/xx/xxxx and the other accounting format

I'm looking to sum the value of the accounting column based on the current month and year.

Preferably without having to add additional columns.

I was following a previously used SUMPRODUCT formula but it kept giving me VALUE! error

=SUMPRODUCT(--(MONTH(V:$V)=8),--(YEAR(V:$V)=2019),AI:$AI)

The formula is pulling the data from a different workbook, so I have editted the columns to simplify for this, although I'm wondering if that's causing the issue, not because the source is incorrect as it's auto-formulated but because it's sourced from a dropbox folder on my pc

Any help appreciated
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi Try below:


Book1
ABD
1DateValueSum
28/1/2019$1,226.00$46,945.00
38/2/2019$1,840.00
48/3/2019$1,332.00
58/4/2019$1,649.00
68/5/2019$1,876.00
78/6/2019$1,298.00
88/7/2019$1,993.00
98/8/2019$1,737.00
108/9/2019$1,122.00
118/10/2019$1,556.00
128/11/2019$1,092.00
138/12/2019$1,953.00
14$43,690.00$1,898.00
158/14/2019$1,630.00
168/15/2019$1,257.00
178/16/2019$1,580.00
188/17/2019$1,060.00
198/18/2019$1,262.00
208/19/2019$1,472.00
218/20/2019$1,606.00
228/21/2019$1,936.00
238/22/2019$1,093.00
248/23/2019$1,274.00
258/24/2019$1,834.00
268/25/2019$1,895.00
278/26/2019$1,408.00
288/27/2019$1,951.00
298/28/2019$1,106.00
308/29/2019$1,273.00
318/30/2019$1,174.00
328/31/2019$1,562.00
339/1/2019$1,811.00
349/2/2019$1,197.00
359/3/2019$1,842.00
369/4/2019$1,062.00
379/5/2019$1,884.00
389/6/2019$1,766.00
399/7/2019$1,428.00
409/8/2019$1,527.00
Sheet4
Cell Formulas
RangeFormula
D2=SUMPRODUCT((TEXT(A2:A40,"myyyy")=TEXT(TODAY(),"myyyy"))*(B2:B40))
 
Upvote 0
it should work across the workbooks even if the workbook where the numbers are stored is closed.
 
Upvote 0
Aryatect said:
Hi Try below:

ABD
DateValueSum

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]$1,226.00[/TD]
[TD="align: right"]$46,945.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8/2/2019[/TD]
[TD="align: right"]$1,840.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8/3/2019[/TD]
[TD="align: right"]$1,332.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8/4/2019[/TD]
[TD="align: right"]$1,649.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8/5/2019[/TD]
[TD="align: right"]$1,876.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]8/6/2019[/TD]
[TD="align: right"]$1,298.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8/7/2019[/TD]
[TD="align: right"]$1,993.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8/8/2019[/TD]
[TD="align: right"]$1,737.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8/9/2019[/TD]
[TD="align: right"]$1,122.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8/10/2019[/TD]
[TD="align: right"]$1,556.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]8/11/2019[/TD]
[TD="align: right"]$1,092.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]8/12/2019[/TD]
[TD="align: right"]$1,953.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]$43,690.00[/TD]
[TD="align: right"]$1,898.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]8/14/2019[/TD]
[TD="align: right"]$1,630.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]8/15/2019[/TD]
[TD="align: right"]$1,257.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]8/16/2019[/TD]
[TD="align: right"]$1,580.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]8/17/2019[/TD]
[TD="align: right"]$1,060.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]8/18/2019[/TD]
[TD="align: right"]$1,262.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]8/19/2019[/TD]
[TD="align: right"]$1,472.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]8/20/2019[/TD]
[TD="align: right"]$1,606.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]8/21/2019[/TD]
[TD="align: right"]$1,936.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]8/22/2019[/TD]
[TD="align: right"]$1,093.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]8/23/2019[/TD]
[TD="align: right"]$1,274.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]8/24/2019[/TD]
[TD="align: right"]$1,834.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]8/25/2019[/TD]
[TD="align: right"]$1,895.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]8/26/2019[/TD]
[TD="align: right"]$1,408.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]8/27/2019[/TD]
[TD="align: right"]$1,951.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]8/28/2019[/TD]
[TD="align: right"]$1,106.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]8/29/2019[/TD]
[TD="align: right"]$1,273.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]8/30/2019[/TD]
[TD="align: right"]$1,174.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]8/31/2019[/TD]
[TD="align: right"]$1,562.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]9/1/2019[/TD]
[TD="align: right"]$1,811.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]9/2/2019[/TD]
[TD="align: right"]$1,197.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]9/3/2019[/TD]
[TD="align: right"]$1,842.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]9/4/2019[/TD]
[TD="align: right"]$1,062.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]9/5/2019[/TD]
[TD="align: right"]$1,884.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]9/6/2019[/TD]
[TD="align: right"]$1,766.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]9/7/2019[/TD]
[TD="align: right"]$1,428.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]9/8/2019[/TD]
[TD="align: right"]$1,527.00[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=SUMPRODUCT((TEXT(A2:A40,"myyyy")=TEXT(TODAY(),"myyyy"))*(B2:B40))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Still getting VALUE! error. If some cells in either column were blank could this cause the issue?
 
Upvote 0
If there is any text there then it can create this error. You can use sumifs, but it does not work if the original file is closed.

SUMIFS option:
Code: 
=SUMIFS($B$2:$B$40,$A$2:$A$40,">="&EOMONTH(TODAY(),-1)+1,$A$2:$A$40,"<="&EOMONTH(TODAY(),0))

Another option can be:

Code: 
=SUM((TEXT(A2:A40,"mmyyyy")=TEXT(TODAY(),"mmyyyy"))*IF(ISNUMBER($B$2:$B$40),$B$2:$B$40,0))

Confirm with Ctrl+Shift+Enter not just enter
 
Upvote 0
Aryatect said:
If there is any text there then it can create this error. You can use sumifs, but it does not work if the original file is closed.

SUMIFS option:
Code: 
=SUMIFS($B$2:$B$40,$A$2:$A$40,">="&EOMONTH(TODAY(),-1)+1,$A$2:$A$40,"<="&EOMONTH(TODAY(),0))

Another option can be:

Code: 
=SUM((TEXT(A2:A40,"mmyyyy")=TEXT(TODAY(),"mmyyyy"))*IF(ISNUMBER($B$2:$B$40),$B$2:$B$40,0))

Confirm with Ctrl+Shift+Enter not just enter
Click to expand...


Aladin Akyurek said:
Control+shift+enter, not just enter:

=
SUM(IF(ISNUMBER(V:$V),IF((MONTH(V:$V)=8)*(YEAR(V:$V)=2019),AI:$AI)))
​
Click to expand...

Wanted to thank you both. The latter options worked perfectly using ctrl+shift+enter.

So that I'm learning, why the use of crtl+shift+enter? If the formula is repeated in the same sheet, different cell, is the same process applicable? I have a running monthly section, so it's the same each month, obviously I adjust the month to suit and confirm entry using crtl+shift+enter each time?

I'm specifically using Aladin's formula, I'm assuming that whether the source file is open or not won't matter, as only applicable to SUMIF right?
 
Upvote 0
Antispam246 said:
Wanted to thank you both. The latter options worked perfectly using ctrl+shift+enter.

So that I'm learning, why the use of crtl+shift+enter? If the formula is repeated in the same sheet, different cell, is the same process applicable? I have a running monthly section, so it's the same each month, obviously I adjust the month to suit and confirm entry using crtl+shift+enter each time?

I'm specifically using Aladin's formula, I'm assuming that whether the source file is open or not won't matter, as only applicable to SUMIF right?
Click to expand...

1. Control+shift+enter is an Excel requirement for signaling Excel that the formula in question is an array-processing formula. Excel's calculation treats such formulas differently (qua algorithms it recruits) than the formulas which do nor require array-processing.

2. You can have the month and year spacifications in cells of their own, say in X2 (8) and Y2 (2019). If you have a different set of specs in X3 and Y3, you can copy down the array formula for X2 and Y2.

3. Array-prcossing formulas work for both open and closed workbooks alike.
 
Upvote 0
Just a follow up on this, using the same formula is it possible to incorporate another requirement for the outcome, in this case, as well as the date a specific text from another column.

I've been trying to figure out if I can use the IF(search("xxx xxx") function but unsure if the original formula would have to be re-worked for it to work.
 
Upvote 0
Antispam246 said:
Just a follow up on this, using the same formula is it possible to incorporate another requirement for the outcome, in this case, as well as the date a specific text from another column.

I've been trying to figure out if I can use the IF(search("xxx xxx") function but unsure if the original formula would have to be re-worked for it to work.
Click to expand...

Which column is it and what is the text you are looking for?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Conditional Formatting for Various Dates
Replies
4
Views
790
MountainFog
M
S
  • Solved
Tenure formula based on end of current month?
Replies
3
Views
332
Joe4
Joe4
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
P
  • Question Question
Summing Data based on a count of prior month data
2
Replies
12
Views
1K
KRice
K
R
  • Question Question
Sumifs or Sumproduct formula within a month with multiple criteria
Replies
6
Views
746
rrmando18
R

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

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