# Drop Down Lists and Countifs Question

#### deanfran

##### Board Regular
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?

#### Attachments

• CIfs.png
36.6 KB · Views: 8

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=COUNTIFS(H:H,IF(J2="","*",J2),I:I,IF(K2="","*",K2))``
where J2 & K2 are the DV cells.

##### Well-known Member
Hi Deanfran,

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

Deanfran.xlsx
ABCDEF
1ColorShapeDropdown ColorDropdown ShapeResult
2RedCircleRed*5
3BlueTriangle
4GreenSquare
5GreenCircle
6GreenTriangle
7BlueSquare
8RedCircle
9RedTriangle
10RedSquare
11RedCircle
12BlueTriangle
13GreenSquare
Sheet1
Cell Formulas
RangeFormula
F2F2=COUNTIFS(\$A\$2:\$A\$9999,\$D\$2,\$B\$2:\$B\$9999,\$E\$2)
Cells with Data Validation
CellAllowCriteria
D2ListRed,Blue,Green,*
E2ListCircle,Triangle,Square,*

#### deanfran

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

Replies
7
Views
107
Replies
6
Views
525
Replies
1
Views
35
Replies
18
Views
192
Replies
4
Views
139

1,129,993
Messages
5,639,423
Members
417,089
Latest member
jonstr101

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back