Lookup and then sum a range

[themick]

Im creating a spreadsheet that takes the year to date from the current year and compares it to the year to date from the same time last year (2013 v. 2012)

Am attaching the watered down spreadsheet for just one person (the final will have more than 50 listed).

I have been looking LOOKUP to find the current month stats but that year to date stat needw to be a sum of the range.

So in the example I changed the date so the month of May is the target (D1) >>=DATE(YEAR(TODAY())-1,MONTH(TODAY())*5,1)

I can get the value returned just for the month of May by using =LOOKUP, but what I need it to do is sum all totals from Jan through the month of May (B2:B6)
(I am setting this up so as the computer date changes (D1), the range will change to include all months from January through the current month.

Am somewhat a novice so stuck on which formula or how to write this, any help would be appreciated.

Mick

[FDibbins]

see if this works for you...
=SUMIFS($B$2:$B$13,$A$2:$A$13,"<="&D1,$A$2:$A$13,">="&A2)

[dilipandey]

Hi Mick,


Use below formula:-

Please Login or Register  to view this content.

see attached:- Book1.xlsx


Regards,
DILIPandey
<click on below * if this helps>

[themick]

Thank you both for your responses; both of these work when I copy/paste and test. The simpler thing to do would be to copy and continue but I like to understand the formulas that I use so i can effectively troubleshoot. thank you so much; have a nice eveing.

[dilipandey]

you are welcome mick...

suggest you to mark this thread as [SOLVED]..

Regards,
DILIPandey
<click on below * if this helps>

[FDibbins]

Mine is a very simple sumifS() function...
=SUMIFS($B$2:$B$13,$A$2:$A$13,"<="&D1,$A$2:$A$13,">="&A2)
the syntax is...
=sumifs(sum-range, criteria1 range, criteria1,criteria2 range, criteria2....etc)
so it says that sum all values in B2:B13, if the date in A2:A13 <= the date in D1, and if the date in A2:A13 greater than the date in A2 (1/1/13)

Hope that helps?

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.