0

I want to create a switch for one of my criteria in a countifs-formula, using a dropdown list. For example:

I want to count all the companies that match criterion 1 (area), and then have a second criterion, size, whose value I can change using a dropdown list, and which can be set to "small", "big" or "both". The "both" part is where I can't figure out what to do.

With "limit values" on a cell (D1) that only allows 1 ("small") and 2 ("big"), and a reference to that cell, the first part is easy:

=countifs(A1:A10,"Munich",B1:B10,"="&D1)

What I'm wondering is what I would have to put into D1 that tells the function to count "all". Normally, you would simply use "<>" instead of "="&"..." to count all the non empty cells, but that's not an an option here. I tried putting * in D1 as well as ="*", but he won't recognize that (I gues due to the values being numbers and not text). I'm stuck.

Thanks for your help.

Edit: For completenes' sake: I just tried to use the formula on a criterion where the data is text, not numbers, and in this case using an asterisk * works just fine. So if the column has "big" and "small" instead of "1" and "2" for values, and you put * into D1, it counts all non empty cells. Hooray :)

Clemens
  • 3
  • 3

1 Answers1

0

Use an IF on the outside that checks for the word "All":

=IF(D1="All",COUNTIF(A:A,"Munich"),COUNTIFS(A:A,"Munich",B:B,D1))

enter image description here

This array formula will do it also:

=SUM(COUNTIFS(A:A,"Munich",B:B,IF(D1="all",{1,2},D1)))

The formula needs to be entered with Ctrl-Shift-enter instead of Enter when exiting edit mode.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Well that solves the problem, but blows up the code tremendously, especially if I want to do the whole shabang a second time within the same formula. – Clemens May 08 '17 at 15:14
  • You could always use SUBTOTAL and filter the data as you want to get the outcome. – Scott Craner May 08 '17 at 15:18
  • Nifty :D Thanks. I'll try it, even though I try to avoid array formulas, because too many of those really weighs my computer down. :) But I guess that's how it's done. – Clemens May 08 '17 at 15:30
  • @Clemens This array is not too taxing as it is only doing 2 iterations of countifs. It is no heaver than that. – Scott Craner May 08 '17 at 15:31
  • @Clemens also if this answered your question please mark as correct by clicking the check mark by the answer. – Scott Craner May 08 '17 at 15:31
  • Done. Unfortunately, I can't upvote it since I lack the reputation. – Clemens May 08 '17 at 15:49