Hello. I've attached a spreadsheet with some line items and dates (by month, quarter, and year). I would like to create a lookup that returns the quarterly numbers (for each year) the yearly numbers, AND the year to date numbers (so, if I wrote August 2011, it would give me the results YTD through August for JUST 2011). I'm open to various ways of doing this, as it helps me learn. Danks.
Book1.xlsx
Please see attached
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Why does this have to be an array? It seems like there could be something simpler.
=SUMPRODUCT(--(Data!$A$5:$A$13=Sheet2!$A$3)*(Data!$B$1:$Y$1=Sheet2!B2),Data!$B$5:$Y$13)
=SUMPRODUCT((Data!$A$5:$A$13=Sheet2!$A$3)*(Data!$B$2:$Y$2=Sheet2!C2)*(Data!$B$1:$Y$1=Sheet2!B2),Data!$B$5:$Y$13)
=SUMPRODUCT((Data!$A$5:$A$13=Sheet2!$A$3)*(Data!$B$3:$Y$3<=Sheet2!D2),Data!$B$5:$Y$13)
The first two formulas work, but the third doesn't. I want the YTD numbers for 2011, so this would be just January and February of 2011. Also, can you explain what the formulas are doing? Thanks.
=SUMPRODUCT((Data!$A$5:$A$13=Sheet2!$A$3)*(YEAR(Data!$B$3:$Y$3)=YEAR(Sheet2!D2))*(MONTH(Data!$B$3:$Y$3)<=MONTH(Sheet2!D2)),Data!$B$5:$Y$13)
These sumproducts work, but couldn't I just use a sum if here? Wouldn't that be easier? Also, what if the table in the "data" tab expands. Instead of B5:Y13, should I extend the number of rows, in case more rows are added?
Last edited by amartino44; 09-06-2013 at 01:20 PM.
I can be done but with two extra function son the top.Is better to use SUMPRODUCT or SUM(IF)) if both: horizontal and vertical ranges are calculated.
Yes you need to expand all ranges to cover more rows/columns.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks