SUMPRODUCT with conditions

[CW]

Hi All

I have a formula which is working as follows:
=SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D452<>""))

This basically counts the numbers of values in a column against a set value.
So for me it counts the number of projects that have dates against the
'testing' phase.

I need to amend this to only count the value if the phase is currently in
progress. This would basically be if D452 < Today() and E4:E52 > Today().

Unfortunately I'm having great difficulty putting this in to a formula.
Could someone please assist?

Thanks
CW

 

[Pete_UK]

Try this:

=SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D452<TODAY())*(Data!
E4:E52>TODAY()))

Hope this helps.

Pete

 

[Jacob Skaria]

Try the below...

=SUMPRODUCT((Data1!B4:B52="Testing")*(ISNUMBER(Data1!D452))*(ISNUMBER(Data1!E4:E52))*(Data1!D452<=TODAY())*
(Data1!E4:E52>=TODAY()))

 

[CW]

Thanks both, both formulas do the job.

Try this:

=SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D452<TODAY())*(Data!
E4:E52>TODAY()))

Hope this helps.

Pete



.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks both, both formulas do the job.






- Show quoted text -