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... |
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... |
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)) ) |
All times are GMT +1. The time now is 11:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com