Vlookup? Combined with IF? Combined with date restrictions... Halp!

[Aimee S.]

How would this look?

I need a formula that looks at Column L for the value of 1, and if there is a value of 1 in Column L, count all occurences of the word "Base" in Column C..... *IF* the date in Column R is between two dates, for example, 3/1/11 and 3/31/11.

Using that same logic I need the same formula but with the change to the *IF* statement. ... If the date in Column R is any date that falls within the current calendar month (adjusts based on what month the current day falls in).

And lastly using that same logic I need the same formula where the if statement is ... If the date in Column R is earlier say than a given date of 5/1/11.

How's that for a tricky question?

 

[Andrew Poulsom]

You can use a formula like this:

=SUMPRODUCT(--(L1:L10=1),--(C1:C10="base"),--(R1:R10>=DATE(2011,3,1)),--(R1:R10<=DATE(2011,3,31)))

Expand the ranges to suit, but make sure that they all have the same number of rows and avoid using entire columns.

 

[Aimee S.]

OO neat!!!!

So any idea what the formulaic language would be so that instead of

--(R1:R10<=DATE(2011,3,31)))

it would pull any date in the present month? I know the formula for the present *day* is =TODAY() ....

 

[Andrew Poulsom]

How about?

=SUMPRODUCT(--(L1:L10=1),--(C1:C10="base"),--(MONTH(R1:R10)=MONTH(TODAY())),--(YEAR(R1:R10)=YEAR(TODAY())))

 

[Aimee S.]

Sadly it returns a #VALUE! error.

The first one works though!

You're so close!!! You can doooo it! hehe ^_^

Maybe it needs something to address the DAY in that it should be between >=1 and <=31?