sumif or sumproduct based on current month and year

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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
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]

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


Control+shift+enter, not just enter:

=
SUM(IF(ISNUMBER(V:$V),IF((MONTH(V:$V)=8)*(YEAR(V:$V)=2019),AI:$AI)))

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
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?

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
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.

Which column is it and what is the text you are looking for?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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