COUNT AND FILTER

[oleg_v]

Hi i need some help.
in the attached file there are 2 sheets "sheet2" and "DATA2"
In the sheet2 i have a table i need to do the filter according to the column "A" each number once because there are repeated numbers.
after filtering column "A" i need to do a second filter in column "Q"
in the table according to the data in sheet "data2" in cells "F3:BX3"
and then to count the row after each second filter:
for example if second filter i done according to F3 cell in sheet "data2"
right the amount in F4 cell and so on.
The results should be that after that i perferm a first filter i need that second filter to run atomaticaly.

Thanks
Oleg

[Bob Phillips]

Is this what you mean

=SUMPRODUCT(--(Sheet2!$A$1:$A$1001=DATA2!$B3),--(Sheet2!$F$1:$F$1001=DATA2!F$2))

[oleg_v]

Hi
thanks for the replay ican not get it to work i am getting a zero in every column in sheet "data2"

[Bob Phillips]

I guess I didn't understand what you wanted then.

[oleg_v]

In the bottom line i just want count the rows after 2 filters

[p45cal]

See F4 of DATA2, copy across and down.
I added unique Item Number list in column E
You realise you have 19 duplicate descriptions in row 3 of DATA2?

[oleg_v]

What do you mean?
what calcolations are you done please explain

[p45cal]

What do you mean?
what calcolations are you done please explain

See F4 of DATA2, copy across and down.

This means I have put a formula into cell F4 of sheet DATA2 in the attached file and I want you to have a look at it.
In your actual file (not this attached file), you would put this formula in F4 and copy it across and down by dragging the small corner box of cell F4 when it is selected.

I added unique Item Number list in column E

This means that I added a list to column E of DATA2 so that the formulae would have something to refer to. That list was created with Advanced filter, Unique items. You don't have to use that, you could, for example, create a Data Validation dropdown in cell E4 of Data2, choose List and choose as the source range a named range containing such a unique list elsewhere in the workbook or perhaps the whole of column Sheet2 column A (but last this would be hard work selecting an item number).

You realise you have 19 duplicate descriptions in row 3 of DATA2?

This means that I noticed that there were duplicate strings in row 3 of DATA2, viz.:
Bad insulation
Bonding of wrong gage ty
Bonding of wrong nickel
Bubbles
Customer return
Damaged gage during myle
damaged potting
Disconnection in the gag
Gage bonded up side down
High OP
Improper closing fixture
Low OP
Nickel rise up
One gage over the other
Raised pad during myler
Strange body under the g
Wrong combination of gag
Wrong gage pos' after pr
Wrong quality of bonding

all appear twice in row 3. This means you will get repeat results in each row, so if you wanted to sum each row to get a total, the totals would probably be wrong.

[oleg_v]

Hi
Please tellme why this formula is not always working?

[p45cal]

All are working in the file I attached