Drop Down Lists and Countifs Question

[deanfran]

I'm learning about this feature of data validation. Two columns, Colors, and Shapes. Using two drop downs to select a shape and color, and Countifs sums the rows that match both criteria. My question was about dealing with making a null selection in one of the drop downs, but the Countifs function still counts the other. In other words, select a color but no shape, and the countifs still counts all the matching colors. Can this be done in the countifs formula or does it require some coding?

 

[Fluff]

How about

=COUNTIFS(H:H,IF(J2="","*",J2),I:I,IF(K2="","*",K2))

where J2 & K2 are the DV cells.

 

[Toadstool]

Hi Deanfran,

You could allow a wildcard selection of "*" so it selects all with that shape/color. e.g.

Deanfran.xlsx
	A	B	C	D	E	F
1	Color	Shape		Dropdown Color	Dropdown Shape	Result
2	Red	Circle		Red	*	5
3	Blue	Triangle
4	Green	Square
5	Green	Circle
6	Green	Triangle
7	Blue	Square
8	Red	Circle
9	Red	Triangle
10	Red	Square
11	Red	Circle
12	Blue	Triangle
13	Green	Square
Sheet1

Cell Formulas
Range	Formula
F2	F2	=COUNTIFS($A$2:$A$9999,$D$2,$B$2:$B$9999,$E$2)

Cells with Data Validation
Cell	Allow	Criteria
D2	List	Red,Blue,Green,*
E2	List	Circle,Triangle,Square,*

 

[deanfran]

Well that was simple. Just add a "None" or * option to the drop down list. Thank you.