Count ifs function using filter

[edsashley]

I have been using the formula =COUNTIFS(F:F,"<>e-learning*",F:F,"*",H:H,"*") to extract data from a work sheet which works fine.

I now want to filter by location, shown in the ‘D’ column. I have just added D:D,$N$16 to the formula to give;

=COUNTIFS(D:D,$N$16,F:F,"<>e-learning*",F:F,"*",H:H,"*")

Where the location is inputted in N16 using a drop down box.

This works fine but I would like to ignore =(D:D,$N$16) if N16 is left blank.

Can anyone help me with this.

 

[shaowu459]

have a try:

=COUNTIFS(D:D,IF($N$16="","*",$N$16),F:F,"<>e-learning*",F:F,"*",H:H,"*")

 

[edsashley]

have a try:

=COUNTIFS(D:D,IF($N$16="","*",$N$16),F:F,"<>e-learning*",F:F,"*",H:H,"*")

Worked almost perfectly thanks. The only issue I have now is the numbers are thrown by the header. Can we ‘-1’ if ‘H1’ reads status?

 

[edsashley]

That’s fine I’ve sorted it now. Thank you.

 

[shaowu459]

That’s fine I’ve sorted it now. Thank you.

you are welcome and thanks for the feedback.

 

[edsashley]

Is there a special way I have to input date into the N16 cell where I want 2 variables to show.

ie before I had for example London north and London south as two variables. I have now entered into the cell ‘London. London north, London south’ in the hopes that this will filter by both north and south instead of two separate filters.