LinkBack URL
About LinkBacks
Hello,
Please help, I need to do a quick SUmIF formula
IF the Range B5:B9 falls under June, then add that particular Line item on Column C
so the formula for C12 should be $200
please see attachment.
thanks,
Hello,
Please help, I need to do a quick SUmIF formula
IF the Range B5:B9 falls under June, then add that particular Line item on Column C
so the formula for C12 should be $200
please see attachment.
thanks,
Can't open your attachment and I don't know if you need SUMIF, but what about
=SUMPRODUCT(--(MONTH(B5:B9)=6),--(C5:C9))
Try:
=SUMIF($B$5:$B$9,">="&EOMONTH(B12,-1)+1,$C$5:$C$9)-SUMIF($B$5:$B$9,">"&EOMONTH(B12,0),$C$5:$C$9)
if you get #NAME error, go to Tools|Addins and check Analysis Toolpak Addin
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Use..in C12
=SUMPRODUCT(--(MONTH($B$5:$B$9)=MONTH(B12)),--($C$5:$C$9)) and drag down
Life's a spreadsheet, Excel!
Say thanks, Click *
note:... for larger lists, SUMIF is more efficient than Sumproduct....
or an pivot table.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Let's take advantage of two things:
1) Excel counts dates forward from a reference (1 = Jan 1, 1900 in the 1900 date system).
2) You're indexing with dates in the B12:B18 array for the output table.
So:
Pull down to c17.
Please Login or Register to view this content.
Because b19 = 0 (when assessed by the SUMIFS), you'll get 0 for December no matter what, though, so that column alone requires a different formula; just chop off the last two arguments so that
Please Login or Register to view this content.
Note - The SUMIFS function will not work in 2003 version
Note also, my original formula was given assuming you are using Excel 2003, as per your user info, but your file is .xlsx.. so you can use simplified:
=SUMIFS($C$5:$C$9,$B$5:$B$9,">="&EOMONTH(B12,-1)+1,$B$5:$B$9,"<="&EOMONTH(B12,0))
thanks so much!! rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks