SUMIFS with DATE criteria (match only year and month)
up vote
0
down vote
favorite
The data I am working with is daily, whereas the data I am importing values from is monthly.
I used SUMIFS and wanted to set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month.
It only returns 0's so far.
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,DATE(YEAR($A2),MONTH($A2),1))/(DATE(YEAR($A2),MONTH($A2)+1,1)-DATE(YEAR($A2),MONTH($A2),1))
excel date sumifs
add a comment |
up vote
0
down vote
favorite
The data I am working with is daily, whereas the data I am importing values from is monthly.
I used SUMIFS and wanted to set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month.
It only returns 0's so far.
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,DATE(YEAR($A2),MONTH($A2),1))/(DATE(YEAR($A2),MONTH($A2)+1,1)-DATE(YEAR($A2),MONTH($A2),1))
excel date sumifs
I don't know the answer, but I feel that the question is "How do I set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month?"
– RichardBJ
Nov 11 at 14:48
Thank you @RichardBJ yes that's what I meant :D
– user10093163
Nov 12 at 5:54
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
The data I am working with is daily, whereas the data I am importing values from is monthly.
I used SUMIFS and wanted to set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month.
It only returns 0's so far.
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,DATE(YEAR($A2),MONTH($A2),1))/(DATE(YEAR($A2),MONTH($A2)+1,1)-DATE(YEAR($A2),MONTH($A2),1))
excel date sumifs
The data I am working with is daily, whereas the data I am importing values from is monthly.
I used SUMIFS and wanted to set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month.
It only returns 0's so far.
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,DATE(YEAR($A2),MONTH($A2),1))/(DATE(YEAR($A2),MONTH($A2)+1,1)-DATE(YEAR($A2),MONTH($A2),1))
excel date sumifs
excel date sumifs
edited Nov 12 at 4:54
asked Nov 11 at 13:53
user10093163
I don't know the answer, but I feel that the question is "How do I set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month?"
– RichardBJ
Nov 11 at 14:48
Thank you @RichardBJ yes that's what I meant :D
– user10093163
Nov 12 at 5:54
add a comment |
I don't know the answer, but I feel that the question is "How do I set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month?"
– RichardBJ
Nov 11 at 14:48
Thank you @RichardBJ yes that's what I meant :D
– user10093163
Nov 12 at 5:54
I don't know the answer, but I feel that the question is "How do I set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month?"
– RichardBJ
Nov 11 at 14:48
I don't know the answer, but I feel that the question is "How do I set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month?"
– RichardBJ
Nov 11 at 14:48
Thank you @RichardBJ yes that's what I meant :D
– user10093163
Nov 12 at 5:54
Thank you @RichardBJ yes that's what I meant :D
– user10093163
Nov 12 at 5:54
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
The problem you have is that you can't change the format of each individual cell in the range that you want to interrogate but you can test if each date in that range falls within a certain date range ( greater than or less than)
So, if the value in cell A2 is the first of the month 1st October 2018 for example then you can use
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2, 'EA-04'!$A:$A,”<”&EDATE($A2,1))
The formula says less than or equal to 1st October 2018 or less that 1st of November 2018
The EDATE(date,1) function adds 1 calendar month to the date in A2 so the 1st of the month becomes the first of the next month regardless of the number of days in the source month.
If the value in cell A2 is not the 1st then you can use this formula to change it to the 1st
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2-(day($A2))+1, 'EA-04'!$A:$A,”<”&EDATE($A2-(day($A2))+1,1))
answered Nov 11 at 20:00
Gordon
1,0151611
Hi! The value in A2 is 31/12/2001, the column it's in is daily. I tried both formulas you gave and it still returns 0.
– user10093163
Nov 12 at 5:50
I also tried setting the criteria as AND(MONTH($A2)=MONTH('EA-04'!$A2),YEAR($A2)=YEAR('EA-04'!$A2). but it also does not work.
– user10093163
Nov 12 at 5:52
Sorry, forgot to mention: The criteria range is monthly, with the day being last day of the month.
– user10093163
Nov 12 at 5:59
it should work as it's checking the whole month. Check that the dates you have listed are stored in date format and not text.
– Gordon
Nov 15 at 1:43
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
The problem you have is that you can't change the format of each individual cell in the range that you want to interrogate but you can test if each date in that range falls within a certain date range ( greater than or less than)
So, if the value in cell A2 is the first of the month 1st October 2018 for example then you can use
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2, 'EA-04'!$A:$A,”<”&EDATE($A2,1))
The formula says less than or equal to 1st October 2018 or less that 1st of November 2018
The EDATE(date,1) function adds 1 calendar month to the date in A2 so the 1st of the month becomes the first of the next month regardless of the number of days in the source month.
If the value in cell A2 is not the 1st then you can use this formula to change it to the 1st
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2-(day($A2))+1, 'EA-04'!$A:$A,”<”&EDATE($A2-(day($A2))+1,1))
answered Nov 11 at 20:00
Gordon
1,0151611
Hi! The value in A2 is 31/12/2001, the column it's in is daily. I tried both formulas you gave and it still returns 0.
– user10093163
Nov 12 at 5:50
I also tried setting the criteria as AND(MONTH($A2)=MONTH('EA-04'!$A2),YEAR($A2)=YEAR('EA-04'!$A2). but it also does not work.
– user10093163
Nov 12 at 5:52
Sorry, forgot to mention: The criteria range is monthly, with the day being last day of the month.
– user10093163
Nov 12 at 5:59
it should work as it's checking the whole month. Check that the dates you have listed are stored in date format and not text.
– Gordon
Nov 15 at 1:43
add a comment |
up vote
0
down vote
The problem you have is that you can't change the format of each individual cell in the range that you want to interrogate but you can test if each date in that range falls within a certain date range ( greater than or less than)
So, if the value in cell A2 is the first of the month 1st October 2018 for example then you can use
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2, 'EA-04'!$A:$A,”<”&EDATE($A2,1))
The formula says less than or equal to 1st October 2018 or less that 1st of November 2018
The EDATE(date,1) function adds 1 calendar month to the date in A2 so the 1st of the month becomes the first of the next month regardless of the number of days in the source month.
If the value in cell A2 is not the 1st then you can use this formula to change it to the 1st
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2-(day($A2))+1, 'EA-04'!$A:$A,”<”&EDATE($A2-(day($A2))+1,1))
answered Nov 11 at 20:00
Gordon
1,0151611
Hi! The value in A2 is 31/12/2001, the column it's in is daily. I tried both formulas you gave and it still returns 0.
– user10093163
Nov 12 at 5:50
I also tried setting the criteria as AND(MONTH($A2)=MONTH('EA-04'!$A2),YEAR($A2)=YEAR('EA-04'!$A2). but it also does not work.
– user10093163
Nov 12 at 5:52
Sorry, forgot to mention: The criteria range is monthly, with the day being last day of the month.
– user10093163
Nov 12 at 5:59
it should work as it's checking the whole month. Check that the dates you have listed are stored in date format and not text.
– Gordon
Nov 15 at 1:43
add a comment |
up vote
0
down vote
up vote
0
down vote
The problem you have is that you can't change the format of each individual cell in the range that you want to interrogate but you can test if each date in that range falls within a certain date range ( greater than or less than)
So, if the value in cell A2 is the first of the month 1st October 2018 for example then you can use
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2, 'EA-04'!$A:$A,”<”&EDATE($A2,1))
The formula says less than or equal to 1st October 2018 or less that 1st of November 2018
The EDATE(date,1) function adds 1 calendar month to the date in A2 so the 1st of the month becomes the first of the next month regardless of the number of days in the source month.
If the value in cell A2 is not the 1st then you can use this formula to change it to the 1st
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2-(day($A2))+1, 'EA-04'!$A:$A,”<”&EDATE($A2-(day($A2))+1,1))
answered Nov 11 at 20:00
Gordon
1,0151611
The problem you have is that you can't change the format of each individual cell in the range that you want to interrogate but you can test if each date in that range falls within a certain date range ( greater than or less than)
So, if the value in cell A2 is the first of the month 1st October 2018 for example then you can use
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2, 'EA-04'!$A:$A,”<”&EDATE($A2,1))
The formula says less than or equal to 1st October 2018 or less that 1st of November 2018
The EDATE(date,1) function adds 1 calendar month to the date in A2 so the 1st of the month becomes the first of the next month regardless of the number of days in the source month.
If the value in cell A2 is not the 1st then you can use this formula to change it to the 1st
=SUMIFS('EA-04'!$D:$D,'EA-04'!$A:$A,”>=”&$A2-(day($A2))+1, 'EA-04'!$A:$A,”<”&EDATE($A2-(day($A2))+1,1))
answered Nov 11 at 20:00
Gordon
1,0151611
answered Nov 11 at 20:00
Gordon
1,0151611
answered Nov 11 at 20:00
Gordon
1,0151611
answered Nov 11 at 20:00
Gordon
1,0151611
1,0151611
Hi! The value in A2 is 31/12/2001, the column it's in is daily. I tried both formulas you gave and it still returns 0.
– user10093163
Nov 12 at 5:50
I also tried setting the criteria as AND(MONTH($A2)=MONTH('EA-04'!$A2),YEAR($A2)=YEAR('EA-04'!$A2). but it also does not work.
– user10093163
Nov 12 at 5:52
Sorry, forgot to mention: The criteria range is monthly, with the day being last day of the month.
– user10093163
Nov 12 at 5:59
it should work as it's checking the whole month. Check that the dates you have listed are stored in date format and not text.
– Gordon
Nov 15 at 1:43
add a comment |
Hi! The value in A2 is 31/12/2001, the column it's in is daily. I tried both formulas you gave and it still returns 0.
– user10093163
Nov 12 at 5:50
I also tried setting the criteria as AND(MONTH($A2)=MONTH('EA-04'!$A2),YEAR($A2)=YEAR('EA-04'!$A2). but it also does not work.
– user10093163
Nov 12 at 5:52
Sorry, forgot to mention: The criteria range is monthly, with the day being last day of the month.
– user10093163
Nov 12 at 5:59
it should work as it's checking the whole month. Check that the dates you have listed are stored in date format and not text.
– Gordon
Nov 15 at 1:43
Hi! The value in A2 is 31/12/2001, the column it's in is daily. I tried both formulas you gave and it still returns 0.
– user10093163
Nov 12 at 5:50
Hi! The value in A2 is 31/12/2001, the column it's in is daily. I tried both formulas you gave and it still returns 0.
– user10093163
Nov 12 at 5:50
I also tried setting the criteria as AND(MONTH($A2)=MONTH('EA-04'!$A2),YEAR($A2)=YEAR('EA-04'!$A2). but it also does not work.
– user10093163
Nov 12 at 5:52
I also tried setting the criteria as AND(MONTH($A2)=MONTH('EA-04'!$A2),YEAR($A2)=YEAR('EA-04'!$A2). but it also does not work.
– user10093163
Nov 12 at 5:52
Sorry, forgot to mention: The criteria range is monthly, with the day being last day of the month.
– user10093163
Nov 12 at 5:59
Sorry, forgot to mention: The criteria range is monthly, with the day being last day of the month.
– user10093163
Nov 12 at 5:59
it should work as it's checking the whole month. Check that the dates you have listed are stored in date format and not text.
– Gordon
Nov 15 at 1:43
it should work as it's checking the whole month. Check that the dates you have listed are stored in date format and not text.
– Gordon
Nov 15 at 1:43
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249426%2fsumifs-with-date-criteria-match-only-year-and-month%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I don't know the answer, but I feel that the question is "How do I set criteria as YEAR & MONTH only. So that in the daily table, it returns the same value for 30/31 days of the corresponding month?"
– RichardBJ
Nov 11 at 14:48
Thank you @RichardBJ yes that's what I meant :D
– user10093163
Nov 12 at 5:54