Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
Hi,
I have a formula which works fine: =SUMPRODUCT( --(Projects_State=F15), --(Projects_Dev_Type=F17), --(Projects_Carrying_Value=F19), --(Projects_Carrying_Value<=F21)) ) "Projects_State", "Projects_Dev_Type" and "Projects_Carrying_Value" are all named ranges. They work fine. F15 = a state in Australia F17 = a text input from a drop down menu F19/F21 = a numerical value. The formula works fine. However, I want cells F15 and F17 to be "optional" whereby if a user *does not* enter a value, it is treated as if all values are required. I have tried, and tried but cannot get anything to work...help please... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
Replace F15 and F17 with
IF(F15="",Projects_State,F15) IF(F17="",Projects_Dev_Type,F17) respectively; something like the below.... Please try and feedback =SUMPRODUCT( --(Projects_State=IF(F15="",Projects_State,F15)), --(Projects_Dev_Type=IF(F17="",Projects_Dev_Type,F17 )), --(Projects_Carrying_Value=F19), --(Projects_Carrying_Value<=F21)) ) -- If this post helps click Yes --------------- Jacob Skaria "Rich" wrote: Hi, I have a formula which works fine: =SUMPRODUCT( --(Projects_State=F15), --(Projects_Dev_Type=F17), --(Projects_Carrying_Value=F19), --(Projects_Carrying_Value<=F21)) ) "Projects_State", "Projects_Dev_Type" and "Projects_Carrying_Value" are all named ranges. They work fine. F15 = a state in Australia F17 = a text input from a drop down menu F19/F21 = a numerical value. The formula works fine. However, I want cells F15 and F17 to be "optional" whereby if a user *does not* enter a value, it is treated as if all values are required. I have tried, and tried but cannot get anything to work...help please... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
Rich wrote...
.... =SUMPRODUCT( --(Projects_State=F15), --(Projects_Dev_Type=F17), --(Projects_Carrying_Value=F19), --(Projects_Carrying_Value<=F21)) ) .... The formula works fine. *However, I want cells F15 and F17 to be "optional" whereby if a user *does not* enter a value, it is treated as if all values are required. One way =SUMPRODUCT( --((Projects_State=F15)+ISBLANK(F15)0), --((Projects_Dev_Type=F17)+ISBLANK(F17)0), --(Projects_Carrying_Value=F19), --(Projects_Carrying_Value<=F21)) ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
SUMPRODUCT help | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
hlp with sumproduct! | Excel Worksheet Functions | |||
sumproduct help | Excel Worksheet Functions |