Count individual visible results of advanced filter?

[RockandGrohl]

Brochure_Region

OptIn_NH_LandMail

Last_Booked

Last_Booked

FamilyFun

FamilyFun

UK

UK

Sports

Sports

Events

Events

EU

EU

Showtime

Showtime

OptIn_ThirdParty

OptIn_DirectEmail

Results

<>0

<>0

>=0

<=1000000

>=1

<=10000

<>0

<>0

>=0

<=1000000

>=1

<=10000

<>0

<>0

>=0

<=1000000

>=1

<=10000

In a previous formula, I helpfully had the name of the criteria in the "OptIn_DirectEmail" column (Column R). That formula is below:

Cells(ActiveCell.Row, "S").FormulaR1C1 = "=SUMPRODUCT(SUBTOTAL(3,OFFSET(DataFeed!R2C35:R" & LastrowDF & "C35,ROW(DataFeed!R2C35:R" & LastrowDF & "C35)-MIN(ROW(DataFeed!R2C35:R" & LastrowDF & "C35)),,1))*(DataFeed!R2C35:R" & LastrowDF & "C35=""" & Cells(ActiveCell.Row, "R").Value & """))"

That doesn't exist in the above table.

What this formula did is return the value of active cell column R inside quote marks, so FamilyFun becomes "FamilyFun"

In the final column, Results, for each row, I need to have the same long formula as above, but in that last bracket it needs to find the name of the column header where the row isn't blank.

For example, in row 2 the formula should be

Cells(ActiveCell.Row, "S").FormulaR1C1 = "=SUMPRODUCT(SUBTOTAL(3,OFFSET(DataFeed!R2C35:R" & LastrowDF & "C35,ROW(DataFeed!R2C35:R" & LastrowDF & "C35)-MIN(ROW(DataFeed!R2C35:R" & LastrowDF & "C35)),,1))*(DataFeed!R2C35:R" & LastrowDF & "C35=""FamilyFun""))"

and in row 3,

Cells(ActiveCell.Row, "S").FormulaR1C1 = "=SUMPRODUCT(SUBTOTAL(3,OFFSET(DataFeed!R2C35:R" & LastrowDF & "C35,ROW(DataFeed!R2C35:R" & LastrowDF & "C35)-MIN(ROW(DataFeed!R2C35:R" & LastrowDF & "C35)),,1))*(DataFeed!R2C35:R" & LastrowDF & "C35=""Events""))"

But instead of doing that cells(activecell.row, "R").value guff, I need to see that on that row, FamilyFun has the criteria applied, so it should be FamilyFun. On the row below, it's Events, so the next formula down would have Events, and so on.

Can you think of a way to do this? Meanwhile I'll keep thinking about it, but all I can think of is doing some kind of index-match where the result is non-blank...

 

[RockandGrohl]

Forget all that, I've decided to just go with a helper column style solution.

I have a new but related question.

This is the formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(DataFeed!$AI$2:$AI$302868,ROW(DataFeed!$AI$2:$AI$302868)-MIN(ROW(DataFeed!$AI$2:$AI$302868)),,1))*(DataFeed!$AI$2:$AI$302868="FamilyFun"))

Brochure_Region

OptIn_NH_LandMail

Last_Booked

Last_Booked

FamilyFun

FamilyFun

UK

UK

Sports

Sports

Events

Events

EU

EU

Showtime

Showtime

OptIn_ThirdParty

OptIn_DirectEmail

Results

<>0

<>0

>=0

<=1000000

>=1

<=10000

=

FamilyFun

3860​

How do I make it so this formula has two criteria? Where DataFeed Column AI (same range) contains "FamilyFun" and where Datafeed column AH contains blank?

Bear in mind, the advanced filter works perfectly, but the formula being placed in the "Results" column is counting the filtered rows and it's only doing so on one criteria.


=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))

This is one way to solve it, and I've tried to apply it as below:

=SUMPRODUCT((DataFeed!AI1:AI302868="FamilyFun")*(DataFeed!AH1:AH302868="")*SUBTOTAL(103,OFFSET(DataFeed!AI1:AI302868,rows,0,1)))

However this results in an error without a suggested correction.

Cheers.

 

[RockandGrohl]

Bump

 

[RoryA]

How did you define

rows

?

 

[RockandGrohl]

How did you define

rows

?

You know what, that is a great question.

Count visible rows with criteria

To count visible rows with criteria, you can use a rather complex formula based on three main functions: SUMPRODUCT, SUBTOTAL, and OFFSET. In the example shown, the formula in H7 is: =SUMPRODUCT((data=H4)*(SUBTOTAL(103,OFFSET(INDEX(data,1),ROW(data)-MIN(ROW(data)),0)))) Where data is the named...

exceljet.net

So looking here, Rows is meant to be equal to the criteria range, but which criteria range I wonder?

 

[RoryA]

So it seems you didn't define it, which is why you get an error.

Try:

=SUMPRODUCT((DataFeed!AI1:AI302868="FamilyFun")*(DataFeed!AH1:AH302868="")*SUBTOTAL(103,OFFSET(DataFeed!AI1:AI302868,row(DataFeed!AI1:AI302868)-MIN(ROW(DataFeed!AI1:AI302868)),0,1)))

 

[RoryA]

Side note: with the number of rows you have, I suspect performance will not be good.

 

[RockandGrohl]

Side note: with the number of rows you have, I suspect performance will not be good.

To be fair, something like this calculates immediately:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(DataFeed!$AI$2:$AI$302868,ROW(DataFeed!$AI$2:$AI$302868)-MIN(ROW(DataFeed!$AI$2:$AI$302868)),,1))*(DataFeed!$AI$2:$AI$302868="FamilyFun"))

I've just tried inputting your formula and it won't go in as a formula.. Just goes in as text. Seems weird

 

[RoryA]

Sounds like your cell is formatted as Text. Change it to General and re-enter the formula.

 

[RockandGrohl]

Great, thanks Rory. So basically, the two criteria are set in the subproduct stage which is the bracketed formula * bracketed formula, I thought I'd have to define the rest of the formula according to the two columns of criteria, AH and AI, but I can see you just define rows etc as AI1:AI300000 which essentially is where I was getting stuck, I thought I'd need two sets of that, one for col AI and one for AH.

As a side note, it calculates instantly :D

Thanks again.