Sum Product

[WDB2]

I have the following formula which is working:

=SUMPRODUCT(--(Sheet2!D:D=Sheet1!B2),(--(Sheet2!F:F="Red")))

I want to add in another argument to say that sheet 2 column L must be greater than today. How do I do this?

Thanks

 

[mikerickson]

What have you tried? How does it fail?

 

[WDB2]

I tried to put something alone the lines of:

=SUMPRODUCT(--(Sheet2!D:D=Sheet1!B2),(--(Sheet2!F:F="Red"),(--(Sheet2!L:L=<TODAY) but it returned 0. I also changed "<TODAY" to 01/11/2011 which definitely should have a count but it still returned 0.

 

[Aladin Akyurek]

I have the following formula which is working:

=SUMPRODUCT(--(Sheet2!D:D=Sheet1!B2),(--(Sheet2!F:F="Red")))

I want to add in another argument to say that sheet 2 column L must be greater than today. How do I do this?

Thanks

Since you are on a recent version of Excel...

=COUNTIFS(Sheet2!D:D,B2,Sheet2!F:F,"Red",Sheet2!L:L,">"&TODAY())

See also your other thread on the same issue.

 

[WDB2]

Many thanks. Works well. If I want to copy the formula to another column to effectively do the same but for dates in the past, what do I replace?

Thanks again

 

[Aladin Akyurek]

Many thanks. Works well.

You are welcome. Thanks for providing feedback.

If I want to copy the formula to another column to effectively do the same but for dates in the past, what do I replace?...

Not sure what you are exactly after...

An example:

Let C2:D2 house a pair of criterion dates with C <= B2. Now invoke:

=COUNTIFS(Sheet2!D:D,B2,Sheet2!F:F,"Red",Sheet2!L:L,">="&C2,Sheet2!L:L,"<="&D2)

The count will now apply to a date range between C2 and D2 inclusive. Does this help?

 

[WDB2]

I just really want the same formula as before but instead of looking at dates in the future, I want to look at dates prior to today.

=COUNTIFS(Sheet2!D:D,B2,Sheet2!F:F,"Red",Sheet2!L:L,">"&TODAY())

Thanks again.

 

[Aladin Akyurek]

I just really want the same formula as before but instead of looking at dates in the future, I want to look at dates prior to today.

=COUNTIFS(Sheet2!D:D,B2,Sheet2!F:F,"Red",Sheet2!L:L,">"&TODAY())

Thanks again.

Would you provide such a date?