SUMIFS Between two dates
=SUMIFS(F13:F18;C13:C18;"Cash money";B13:B18;criteria2)
How to make this criteria2 to sum between dates 1/1/2012 and 2/1/2012?
I've found some answers for earlier version of excel, but non of them worked in excel 2010.
Simple, I want so sum between dates January 1 2012 and February 1 2012 and one more criteria which is text match.
thanks
try something like this....
=SUMIFS(F13:F18;C13:C18;"Cash money";B13:B18;">="&1/1/2012;B13:B18;"<"&2/1/2012)
essentially, you just need to add 2 criteria, 1 for each date
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Here's another possibility...
=SUMPRODUCT(--(C13:C18="Cash money");--(TEXT(B13:B18;"mmmyyyy")="Jan2012");F13:F18)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for the repys FDibbins and Toni.
I had to modify FDibbins's suggestion into this:
=SUMIFS(F13:F18;C13:C18;"Cash money";B13:B18;">="&DATE(2013;1;1);B13:B18;"<"&DATE(2013;2;1))
and it worked, thanks a lot.
Just a suggestion, but if you are going to change the criteria on your responders, you should at least acknowledge (AND post ) it ... (AND changing the year is definitely a BIG criteria change !)Originally Posted by nsens
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Sorry dredwolf, I didn't understand your suggestion, I'm a beginner.
And one other thing, I forgot that I have to substract two sum ranges for these criterias and this doesn't work:
=SUMIFS(F13:F18-G13:G18;C13:C18;"Cash money";B13:B18;">="&DATE(2013;1;1);B13:B18;"<"&DATE(2013;2;1))
so I have to use:
=SUMIFS(F13:F18;C13:C18;"Cash money";B13:B18;">="&DATE(2013;1;1);B13:B18;"<"&DATE(2013;2;1))
-
SUMIFS(G13:G18;C13:C18;"Cash money";B13:B18;">="&DATE(2013;1;1);B13:B18;"<"&DATE(2013;2;1))
Is there a way to use F13:F18-G13:G18 under sum_range so I don't have to write the whole thing twice like in the example above?
I thought my suggestion was quite straight forward...however...
to make the formulas shorter, try using named ranges, they can simplify the appearrance of the formula at least
Oh, I get it, thanks for the advice.I thought my suggestion was quite straight forward...however...
to make the formulas shorter, try using named ranges, they can simplify the appearrance of the formula at least
hrrm, well without a sample to work on,I can only make a suggestion here, but seeing as SUM(F13:F18-G13:G18) entered as an Array formula works, it MIGHT work for sumifs as well, but it is just a guess as I am not willing to try to re-create a workbook for it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks