CODEWITHSUNDEEP
excelexcel-formulacountif
Sean
Sean

Reputation: 15164

Excel COUNTIF with optional parameter

I have the formula:

=COUNTIFS(Table1[SessionDate], "<" & $B3, Table1[District], CHART!$J$1)

Cells in B3, B4, B5, etc. have a date value that I want to sum if the date value of the data row (SessionDate) is less than this value. Works fine.

Cell J1 has a drop-down list of districts, which the user can choose from. Works fine.

However, if the user clears the drop-down, I'd like it to count data for ALL districts.

I can do this using something like:

=IF(J1="", [without_district_filter], [with_district_filter])

... but I need a couple of these filters, and would prefer not to have a bunch of messy nested IFs.

Is there a cleaner way?

Upvotes: 1

Views: 654

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

The lines =COUNTIFS(Table1[SessionDate], "<" & $B3, Table1[District], "<>") or =COUNTIFS(Table1[SessionDate], "<" & $B3, Table1[District], "*") will count all rows where Table1[SessionDate] < $B3 and Table1[District] is not blank.

You can use an IF statement within the COUNTIFS to check this:

=COUNTIFS(Table1[SessionDate], "<" & $B3, Table1[District], IF(Len(CHART!$J$1)<1,"<>",CHART!$J$1))

This will not work if you have any rows where Table1[District] is empty

Upvotes: 0

JoeJam
JoeJam

Reputation: 371

Create a District called "ALL" that includes all of the rows and make the user select that to count all of the data.

Upvotes: -2

Related Questions