sumifs not working quite right

One option

Put the number of the month in cell E1, rather than the text, and use:

=SUMIFS(B2:B7,A2:A7,">="&DATEVALUE("01/"&E1&"/"&E2),A2:A7,"<"&DATEVALUE("01/"&E1+1&"/"&E2))


Regards, TMS

Hi try this

Formula:

Please Login or Register  to view this content.

but in E1 type 12-01-01 with such format "mmm"

@tom1977

Doesn't take into account the year. Have tried this, but just returns 0, confused :S

Please Login or Register  to view this content.

@TMShucks

Bit messy but works, thanks

How is it messy? You've split the date parameters across two cells. You could just put the date in cell E1 as 01/01/2012 and format as "mmm". And in cell E2, if you wanted, you could put =E1 and format as "yyyy". So then you'd see "Jan 2012"

Then you could use the SUMPRODUCT with MONTH and YEAR just referring to cell E1.

I suspect that SUMIFS would be better but you'd need to adjust the formula I provided ... references to E1 would become MONTH(E1) and E2 would be YEAR(E1)

Regards, TMS

Not messy as such, just a bit long winded, was hoping for a short formula so that the less excel minded can understand in the office.

I've adapted it to this Sumifs of dates.xlsx like you suggested.

=SUMPRODUCT((MONTH($A$2:$A$7)=MONTH($E$3))*(YEAR($A$2:$A$7)=YEAR($E$3))*($B2:$B$7))

Please don't quote whole posts as it just makes the thread untidy and long winded.

Regards, TMS

the correct sumproduct version is
=SUMPRODUCT((MONTH($A$2:$A$7)=MONTH($E$3))*(YEAR($A$2:$A$7)=YEAR($E$3)),$B$2:$B$7)
or
=SUMPRODUCT(--(TEXT($A$2:$A$7,"yyyymm")=TEXT($E$3,"yyyymm")),$B$2:$B$7)

Here is another option:

=SUMIFS(B2:B7,A2:A7,">="&EOMONTH(E7,-1)+1,A2:A7,"<"&EOMONTH(E7,0))

Originally Posted by TMShucks

Please don't quote whole posts as it just makes the thread untidy and long winded.

I that sarcasm? Cheers for the help

I that sarcasm?

No, it's not. It's actually rule 12:

12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Sometimes a moderator will tidy up after you ... but they're not your mother, so you should do it yourself ... that's sarcasm.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

@benoj2005: no problem.

@JosephP: thanks for the rep. And, of course, for your imaginative alternatives. It always impresses me that there are so many different approaches to problems