SUMPRODUCT with conditions
 

Hi All

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

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 D4:D52 < Today() and E4:E52 Today().

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

Thanks
CW

SUMPRODUCT with conditions
 

Try this:

=SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D4:D52 <TODAY())*(Data!
E4:E52TODAY()))

Hope this helps.

Pete

On Apr 28, 9:17*am, CW wrote:
Hi All

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

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 D4:D52 < Today() and E4:E52 Today().

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

Thanks
CW

SUMPRODUCT with conditions
 

Try the below...

=SUMPRODUCT((Data1!B4:B52="Testing")*(ISNUMBER(Dat a1!D4:D52))*(ISNUMBER(Data1!E4:E52))*(Data1!D4:D52 <=TODAY())*
(Data1!E4:E52=TODAY()))

--
Jacob (MVP - Excel)


"CW" wrote:

Hi All

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

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 D4:D52 < Today() and E4:E52 Today().

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

Thanks
CW

SUMPRODUCT with conditions
 

Thanks both, both formulas do the job.



"Pete_UK" wrote:

Try this:

=SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D4:D52 <TODAY())*(Data!
E4:E52TODAY()))

Hope this helps.

Pete

On Apr 28, 9:17 am, CW wrote:
Hi All

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

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 D4:D52 < Today() and E4:E52 Today().

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

Thanks
CW

.

SUMPRODUCT with conditions
 

You're welcome - thanks for feeding back.

Pete

On Apr 28, 11:10*am, CW wrote:
Thanks both, both formulas do the job.



"Pete_UK" wrote:
Try this:

=SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D4:D52 <TODAY())*(Data!
E4:E52TODAY()))

Hope this helps.

Pete

On Apr 28, 9:17 am, CW wrote:
Hi All

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

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 D4:D52 < Today() and E4:E52 Today().

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

Thanks
CW

.- Hide quoted text -

- Show quoted text -