SUMPRODUCT

[Rich]

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...

 

[Jacob Skaria]

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))
)

 

[Jacob Skaria]

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))
)

 

[Harlan Grove]

=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))
)

 

[Harlan Grove]

=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))
)