CODEWITHSUNDEEP
excelvlookup
Andrew
Andrew

Reputation: 240

Lookup "Text" Variable between dates

Let me preface this question with: The data that I was provided is not in the most logical order and the work involved in scrubbing to a more uniform it would be time consuming.

I am compiling a sales sheet to collect an overall yearly view of each product's revenue. I am currently attempting to search for the product name within the sheets required within a main sheet (all within the same workbook).

See sample output below:

Year 
2014            Jan    Feb    Mar   ...    Nov    Dec
  Product 1
  Product 2
  Product 3

2015            Jan    Feb    Mar   ...    Nov    Dec
  Product 1
  Product 2
  Product 3

Sample Input

...    F   ...       N       ...      T       ...
...  Date  ...    Product    ...    Amount    ...
------------------------------------------------------

10/03/15   ...  Prod. 1  ...   $1000.00   ...
04/05/15   ...  Prod. 3  ...   $3000.00   ...
02/09/15   ...  Prod. 1  ...   $2000.00   ...

I would like to break it down based on month and product which is where I am running into issues. Currently I am (attempting) using: 

=SUMIFS('2015'!R:R,'2015'!F:F,"Product 1",">=1/1/2015",'2015'!F:F,"<=1/31/2015")

Any help provided would be greatly appreciated!

Upvotes: 0

Views: 34

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

SUMIFS() is the best way to go. But one can make the criteria more dynamic so the formula can be copied from cell to cell instead of having the values "hard coded" in the formula.

To do such ensure that your month row is Actual dates that have a number format of mmm instead of text.

enter image description here

As you can see the Jan cell is actually 1/1/2015. We could have made this a little more dynamic also. Instead of the year being hard coded, since I put the year in A2, I could have used this formula:

=DATE($A$2,Column(A:A),1)

And drag/copied across. This would have put the first day of each month.

Again do a custom format of mmm

Then the formula would be:

=SUMIFS('2015'!$T:$T,'2015'!$N:$N,$A3,'2015'!$F:$F,">=" & EOMONTH(B$2,-1)+1,'2015'!$F:$F,"<=" & EOMONTH(B$2,0))

This formula then is dragged/copied across and down.

enter image description here

Upvotes: 1

Related Questions