Hi Team,
I want to find spend, save and loss numbers for the names in the drop down by month, quarter, and YTD. It is in a particular format and I am not sure what kind of formula to use in this case.
I have attached the test file for better understanding
Please note Month and Quarter also have drop down and by selecting them the formula should provide the Spend, save and los numbers.
Appreciate your help!
Thanks!
Guru
This is what I have so far.
For the months array enter this formula in B2 an fill across.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
For quarters array entered in B3 and fill across.Formula:Please Login or Register to view this content.
The YTD I haven't done, yet.
Dave
Hi Dave,
Thank you so much it works beautifully. I hope you could help with YTD too whenever you find time.
Thanks a ton again, really appreciate your help.
4Gurus
Hi Dave,
Sorry, I forgot to ask one more thing what kind of edit should I make in the Qtr formula so that I get the sum of Qtrs spend, loss, and save for all the people instead of individual.
Thank you!
4Gurus
Hi 4gurus,
Been working on some alternative formulas.
The YTD thing is throwing me so the formula in row 4 is a work in progress.
I've taken the liberty of inserting two helper rows above the source data.
The reason being merged cells cause havoc for formulas. The helper rows are a way out.
In B6 filled down and across to S7 of the attached this helper row formula.Formula:Please Login or Register to view this content.
Are we OK so far?
Dave,
Just a thought:
I would make your date helper (row 7) actual dates (01/01/2016 for first three) ..etc
then use for YTD
=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*(MONTH($B$7:$S$7)<=MONTH(TODAY())*($A$11:$A$14=$A$1)))
I would go further and put the dates in row 9 in the above, formatted as "mmm"
This is because I do not like (nor advocate) the use of simple Text dates (my bee in the bonnet!!)
Using dates, then the quarter calculations could be on start/end month for a quarter,
@ John,
I like it.
In fact I borrowed your idea and extended it a bit to the Data Validation lists and the original month row (9).
I am kind of stuck right now on how to explain simply what I've done though. Quite a few changes. I am afraid I will unnecessarily risk introducing confusion.
I would suggest a slight change to your formula. OP wants YTD to be allRemoving the name ($A$11:$A$14=$A$1) criteria does the trick.what kind of edit should I make in the Qtr formula so that I get the sum of Qtrs spend, loss, and save for all the people instead of individual.
=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*(MONTH($B$7:$S$7)<=MONTH(TODAY())))
Thanks for the backup and assist. It's appreciated.I love team-builds.
@Dave,
Glad to have helped and thank you for the feedback.
I too like "team builds" not least because it fills in the many holes in my knowledge.
Hi Dave and John,
Thank you both for your valuable help. Dave I think the helper columns has simplified the formula which is great; however when I try sumproduct formula to get total spend achieved by team for the Qtr1 or Qtr2 using drop down I get an error like #value! not sure how link the formula to the drop down.
Thanks!
4Gurus
In W1 to W4 add "Qtr" start dates
01/01/2016
01/04/2016
01/07/2016
01/10/2016
For Qtr team total:
Spend
=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*($B$9:$S$9>=VLOOKUP($A$3,$V$1:$W$4,2,0))*($B$9:$S$9<=EOMONTH(VLOOKUP($A$3,$V$1:$W$4,2,0),2)))
Copy across for "Save" & "Loss"
See table in A17 etc
OR
this removes need for W1:W4 data
=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*($B$9:$S$9>=DATE(2016,(RIGHT($A3)+0-1)*3+1,1))*($B$9:$S$9<=EOMONTH(DATE(2016,(RIGHT($A3)+0-1)*3+1,1),2)))
Last edited by JohnTopley; 07-22-2016 at 08:27 AM.
Hi John,
Thank you so much for your assistance and your formula works like a charm now the only thing I am left with is to find monthly spend, loss, and save for individual and the team; however, I have tried the formula SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=$B1)*($A$11:$A$14=$A$1)*($B$7:$S$7=A2)) but I get Zero not sure what I could be missing.
Please let me know what changes I need to make to get the numbers for individual and team for the month
Thanks!
4Gurus
You have these formulae: in B2 to D2 and B5 to D5
formula from B2
=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*($B$9:$S$9>=DATE(2016,MONTH($A$2&1),1))*($B$9:$S$9<=EOMONTH(DATE(2016,MONTH($A$2&1),1),0)*($A$11:$A$14=$A$1)))
from B5
=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*($B$9:$S$9>=DATE(2016,MONTH($A$2&1),1))*($B$9:$S$9<=EOMONTH(DATE(2016,MONTH($A$2&1),1),0)))
Last edited by JohnTopley; 07-23-2016 at 03:10 PM.
Hi John,
Thank you so much for your help on the formulae. I never knew how beautifully sumproduct works (unless one knows how to effectively use it) and it is an alternate for match and index formula.
Once again many thanks to Dave and you for being my savior/saviour
Thanks!
4Gurus
You are very welcome. Thank you for the feedback.
You are welcome. Thank you for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks