Adding a Date Criteria to a SUMPRODUCT Formula

[Fujirich]

Hi folks!

I've been delving back into SUMPRODUCT today after not have the need to use it for some time, and as usual I could not have accomplished what I needed without the kind help of people on this forum. I have another issue to tackle that I'm not sure about...

Here's the current formula in question -

=SUMPRODUCT(--(INDIRECT(SUBSTITUTE(SelectedCriteria," ","_"))=SelectedValue),--(Current_Status="Gained"),--(Product_Group=VLOOKUP(D295,Admin!$B$282:$C$289,2,FALSE)),Sales)

I need to add another criteria to this - that being a specific month

In my data, I have long column I've converted to a range called "Date_Concluded". These dates are expressed m/d/yyy.

I need to segregate Gains by month, and since this is a fiscal year running April to March, the current years months would span 4/08 to 3/09.

So my question is: how do I add an additional criteria to that formula that looks at Date_Concluded range and uses just the values for a specfic month?

Thanks in advance for any help!

 

[Aladin Akyurek]

Hi folks!

I've been delving back into SUMPRODUCT today after not have the need to use it for some time, and as usual I could not have accomplished what I needed without the kind help of people on this forum. I have another issue to tackle that I'm not sure about...

Here's the current formula in question -

=SUMPRODUCT(--(INDIRECT(SUBSTITUTE(SelectedCriteria," ","_"))=SelectedValue),--(Current_Status="Gained"),--(Product_Group=VLOOKUP(D295,Admin!$B$282:$C$289,2,FALSE)),Sales)

I need to add another criteria to this - that being a specific month

In my data, I have long column I've converted to a range called "Date_Concluded". These dates are expressed m/d/yyy.

I need to segregate Gains by month, and since this is a fiscal year running April to March, the current years months would span 4/08 to 3/09.

So my question is: how do I add an additional criteria to that formula that looks at Date_Concluded range and uses just the values for a specfic month?

Thanks in advance for any help!

...,--(MONTH(Range)=Month),--(YEAR(range)=Year),...

 

[Aladin Akyurek]

...,--(MONTH(Range)=Month),--(YEAR(range)=Year),...

To be consistent...

...,--(Date_Concluded-DAY(Date_Concluded)+1="2008-10-01"+0),...

which is a shorter expression...

 

[Fujirich]

Thanks Aladin - very appreciated as always!

A small follow up...

I'm assuming that I could substitute a cell reference for this part of the equation -

="2008-10-01"

If so, the date in said cell would have to be the first of each month in question.

Sound right?


Thanks again!!

 

[Aladin Akyurek]

Thanks Aladin - very appreciated as always!

A small follow up...

I'm assuming that I could substitute a cell reference for this part of the equation -

="2008-10-01"

If so, the date in said cell would have to be the first of each month in question.

Sound right?


Thanks again!!

Yes.