CODEWITHSUNDEEP
excelvbaexcel-formula
steve
steve

Reputation: 431

Filter filter criteria and then apply in countif statement in Excel

I have a table of filter criteria like this:

+----------+----------+------+------+------+
| Category | SpecName | Spec | Pass | Fail |
+----------+----------+------+------+------+
| A        | S1       |    3 |      |      |
| A        | S2       |    4 |      |      |
| B        | S1       |    5 |      |      |
| C        | S1       |    2 |      |      |
+----------+----------+------+------+------+

I have a table I want to apply the filter criteria to like this:

+----------+----+----+
| Category | S1 | S2 |
+----------+----+----+
| A        |  5 |  3 |
| B        |  4 |    |
| A        |  5 |  5 |
| C        |  2 |    |
| A        |  2 |  6 |
+----------+----+----+

I want to fill the Pass and Fail columns in the filter criteria table with a count of items in second table with values >= the corresponding spec, like so.

+----------+----------+------+------+------+
| Category | SpecName | Spec | Pass | Fail |
+----------+----------+------+------+------+
| A        | S1       |    3 |    1 |    2 |
| A        | S2       |    4 |    1 |    2 |
| B        | S1       |    5 |    0 |    1 |
| C        | S1       |    2 |    1 |    0 |
+----------+----------+------+------+------+

Here are steps for how I might do it in a scripting language:

  1. Filter first table to get all spec filter criteria for the Category on that row, as follows for the first row.
+----------+----------+------+
| Category | SpecName | Spec |
+----------+----------+------+
| A        | S1       |    3 |
| A        | S2       |    4 |
+----------+----------+------+
  1. Copy table 2 to a variable iTable
+----------+----+----+
| Category | S1 | S2 |
+----------+----+----+
| A        |  5 |  3 |
| B        |  4 |    |
| A        |  5 |  5 |
| C        |  2 |    |
| A        |  2 |  6 |
+----------+----+----+
  1. For each spec name: Find column in iTable with spec name Filter spec name column in iTable by spec
  2. After all filters applied, we would have:
+----------+----+----+
| Category | S1 | S2 |
+----------+----+----+
| A        |  5 |  5 |
+----------+----+----+
  1. Then just count the rows in iTable and assign to the cell in Pass column of the criteria table

Is this possible with Excel formulas?

If not, does anyone know how to do it with VBA?

Upvotes: 0

Views: 140

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

Looking at an alternative layout for you spec criteria. Expand you columns to suit your need.

Alt Table 1 Layout

With each spec criteria being its own column life gets really easy. You just need to adjust your formula to match the number of criteria you have.

Based on the table at the end for layout, place the following formula in D3 and copy down as required.

=SUMPRODUCT(($G$2:$G$6=A3)*($H$2:$H$6>=B3)*($I$2:$I$6>=C3))

That will give you a count of passing all criteria. Its also a function that performs array like calcs. It could be repeated in the next column but in order to reduce dependency on array calculation and potentially speed things up depending on the amount of data to check, place the following in the top of the fail column and copy down as required:

=COUNTIF($G$2:$G$6,A3)-D3

Basically it subtracts the passes from the total count. This assumes you can only have PASS and FAIL as options.

POC

Upvotes: 1

Related Questions