Hi all,
I'm looking for a formula that will sum up employee bonuses by month, and by department. The format of the spreadsheet is attached. I'm looking for formulas that will complete the yellow cells.
Any help would be greatly appreciated!
bonus calc.xlsx
D23=SUMPRODUCT(($B$5:$B$14=$A23)*(TEXT($C$5:$C$14,"MMYY")=TEXT(D$22,"MMYY"))*($D$5:$D$14))
Try the above and copy across
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Or
=SUMIFS($D$5:$D$14, $B$5:$B$14, $A23, $C$5:$C$14, ">=" & D$22, $C$5:$C$14, "<" & EDATE(D$22, 0))
Last edited by shg; 07-01-2015 at 12:36 PM. Reason: Corrected edate(..., 1) to edate(..., 0)
Entia non sunt multiplicanda sine necessitate
Or
=SUMPRODUCT(--($B$5:$B$14=$A23),$D$5:$D$14, --(MONTH($C$5:$C$14)=MONTH(D$22)))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I'll not bother with my solution - now you have plenty. Just note that there's a mistake in P22 - it refers to the wrong year!!
It may be better to use =EOMONTH(D22,0)+1 in E22 and drag across, to set the dates...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks