PDA

View Full Version : COUNT AND FILTER



oleg_v
12-14-2010, 05:33 AM
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
12-14-2010, 06:24 AM
Is this what you mean

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

oleg_v
12-14-2010, 06:31 AM
Hi
thanks for the replay ican not get it to work i am getting a zero in every column in sheet "data2"

Bob Phillips
12-14-2010, 06:38 AM
I guess I didn't understand what you wanted then.

oleg_v
12-14-2010, 06:39 AM
In the bottom line i just want count the rows after 2 filters

p45cal
12-14-2010, 07:03 AM
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
12-14-2010, 07:12 AM
What do you mean?
what calcolations are you done please explain

p45cal
12-14-2010, 07:38 AM
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 EThis 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
12-15-2010, 01:09 AM
Hi
Please tellme why this formula is not always working?

p45cal
12-15-2010, 01:38 AM
All are working in the file I attached