If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMPRODUCT with conditions
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 |
#2
|
|||
|
|||
SUMPRODUCT with conditions
Try this:
=SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D452 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!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 |
#3
|
|||
|
|||
SUMPRODUCT with conditions
Try the below...
=SUMPRODUCT((Data1!B4:B52="Testing")*(ISNUMBER(Dat a1!D452))*(ISNUMBER(Data1!E4:E52))*(Data1!D452 =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!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 |
#4
|
|||
|
|||
SUMPRODUCT with conditions
Thanks both, both formulas do the job.
"Pete_UK" wrote: Try this: =SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D452 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!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 . |
#5
|
|||
|
|||
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!D452 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!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 .- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|