Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with conditions | Excel Worksheet Functions | |||
Sumproduct with conditions | Excel Discussion (Misc queries) | |||
SUMPRODUCT with conditions | Excel Worksheet Functions | |||
Conditions in sumproduct | Excel Worksheet Functions | |||
sumproduct three conditions | Excel Worksheet Functions |