Remove a criteria from countif based on user selection

[JT]

Hello!

I am trying to design an output for my spreadsheet which shows the
number of items in a list that match user selected criteria. For
example with the following data set:

A B
1 Male? Over 18?
2 TRUE TRUE
3 TRUE FALSE
4 FALSE TRUE
5 FALSE FALSE
6 FALSE TRUE

I would use the following function:

=COUNTIFS($A$2:$A$6,[User input 1],$B$2:$B$6,[User input 2])

Where the user inputs are TRUE or FALSE drop down boxes that allow the
user to select the gender and age categories they wish to count.

I wish to amend this formula so that I can have a "Don't Care" option
in my user inputs. This would allow the user to count the number of
items that match only one of the criteria.

The real life version of this has a large number of columns all
containing booleans.

I do not want to use a pivot table for this.

Does anybody know how I could achieve this?

Thanks

John

 

[Don Guillett Excel MVP]

Hello!

I am trying to design an output for my spreadsheet which shows the
number of items in a list that match user selected criteria.  For
example with the following data set:

   A             B
1 Male?      Over 18?
2 TRUE       TRUE
3 TRUE       FALSE
4 FALSE     TRUE
5 FALSE     FALSE
6 FALSE     TRUE

I would use the following function:

=COUNTIFS($A$2:$A$6,[User input 1],$B$2:$B$6,[User input 2])

Where the user inputs are TRUE or FALSE drop down boxes that allow the
user to select the gender and age categories they wish to count.

I wish to amend this formula so that I can have a "Don't Care" option
in my user inputs.  This would allow the user to count the number of
items that match only one of the criteria.

The real life version of this has a large number of columns all
containing booleans.

I do not want to use a pivot table for this.

Does anybody know how I could achieve this?

Thanks

John

How about a worksheet_change event in the sheet module tied to the
drop down that amends the formula(s).

 

[JT]

Thanks Don

I'm trying to do this without the use of VBA and think I'm on the
verge of cracking it. I can use sumproduct to achieve the same result
as countifs as follows:

A B
1 Male? Over 18?
2 TRUE TRUE
3 TRUE FALSE
4 FALSE TRUE
5 FALSE FALSE
6 FALSE TRUE

=SUMPRODUCT(1*($A$2:$A$6=[User input 1]),1*($B$2:$B$6,[User input 2]))

Where the user inputs are TRUE or FALSE drop down boxes that allow the
user to select the gender and age categories they wish to count.

If the user inputs are both true, this is equivalent to the following:
=SUMPRODUCT({1,1,0,0,0},{1,0,1,0,1})

To add a facility to allow the user to state that they don't care
whether a criteria is true or false, all I need to do is find a way of
forcing all values in the relevant array to be 1. I have managed to
force them all to be zero simply by multiplying the array by zero but
this doesn't give me the result I need.

Can anyone help me with this final step?

Thanks

John

 

[AB]

How about:

=SUMPRODUCT((IF([UserInput 1]="Don'tCare",1;$A$2:$A$6=[UserInput
1]))*($B$2:$B$6=[UserInput 2]))

NB!! this is an arrayEnter formula, i.e., enter it with Ctrl+Shift
+Enter - so that the IF function would return an array.

Does it work?

 

[JT]

How about:

=SUMPRODUCT((IF([UserInput 1]="Don'tCare",1;$A$2:$A$6=[UserInput
1]))*($B$2:$B$6=[UserInput 2]))

NB!! this is an arrayEnter formula, i.e., enter it with Ctrl+Shift
+Enter - so that the IF function would return an array.

Does it work?

Thanks Don

I'm trying to do this without the use of VBA and think I'm on the
verge of cracking it.  I can use sumproduct to achieve the same result
ascountifsas follows:

   A             B
1 Male?      Over 18?
2 TRUE       TRUE
3 TRUE       FALSE
4 FALSE     TRUE
5 FALSE     FALSE
6 FALSE     TRUE

=SUMPRODUCT(1*($A$2:$A$6=[User input 1]),1*($B$2:$B$6,[User input 2]))

Where the user inputs are TRUE or FALSE drop down boxes that allow the
user to select the gender and age categories they wish to count.

If the user inputs are both true, this is equivalent to the following:
=SUMPRODUCT({1,1,0,0,0},{1,0,1,0,1})

To add a facility to allow the user to state that they don't care
whether a criteria is true or false, all I need to do is find a way of
forcing all values in the relevant array to be 1.  I have managed to
force them all to be zero simply by multiplying the array by zero but
this doesn't give me the result I need.

Can anyone help me with this final step?

John- Hide quoted text -

- Show quoted text -

Is there a way of doing this without an array formula?

 

[AB]

Not that i'm aware of. I'm more like a vba-kinda-guy and hence my
formulas tend to be not as sophisticated.
At the same time i'm thinking that you need an array to be returned
for that sumproduct to calculate correctly and for IF to return an
array it needs to be array-entered...
There is nothing wrong with array formulas it's just that user needs
to know/remember how to enter them...

How about:

=SUMPRODUCT((IF([UserInput 1]="Don'tCare",1;$A$2:$A$6=[UserInput
1]))*($B$2:$B$6=[UserInput 2]))

NB!! this is an arrayEnter formula, i.e., enter it with Ctrl+Shift
+Enter - so that the IF function would return an array.

Does it work?

Thanks Don
I'm trying to do this without the use of VBA and think I'm on the
verge of cracking it.  I can use sumproduct to achieve the same result
ascountifsas follows:
   A             B
1 Male?      Over 18?
2 TRUE       TRUE
3 TRUE       FALSE
4 FALSE     TRUE
5 FALSE     FALSE
6 FALSE     TRUE
=SUMPRODUCT(1*($A$2:$A$6=[User input 1]),1*($B$2:$B$6,[User input2]))
Where the user inputs are TRUE or FALSE drop down boxes that allow the
user to select the gender and age categories they wish to count.
If the user inputs are both true, this is equivalent to the following:
=SUMPRODUCT({1,1,0,0,0},{1,0,1,0,1})
To add a facility to allow the user to state that they don't care
whether a criteria is true or false, all I need to do is find a way of
forcing all values in the relevant array to be 1.  I have managed to
force them all to be zero simply by multiplying the array by zero but
this doesn't give me the result I need.
Can anyone help me with this final step?
Thanks
John- Hide quoted text -

- Show quoted text -

Is there a way of doing this without an array formula?- Hide quoted text -

- Show quoted text -