Subtotal??

[plasteredric]

20664

Im in need of a formula that only includes visible rows so that I can use filters on my sheet to refine an outcome.

attached is sample sheet if anyone can help.

Thanks

[mdmackillop]

No data to test this on.

[plasteredric]

No data to test this on.

Not sure what you're looking at but there is data in the sheet where it is needed

[p45cal]

Not sure what you're looking at but there is data in the sheet where it is neededHe's looking at your attached file. There's no data to test this on.

[plasteredric]

He's looking at your attached file. There's no data to test this on.

If you look at the attached pic there is data in the cells that are to be referenced

[plasteredric]

I have this formula for cell 'AH6' which works to count the number or instances in the column, i just don't know how to only count the rows where there is a value in the AH column

=SUMPRODUCT(($Q$30:$Q$9999=$Y6)*(SUBTOTAL(103,OFFSET($Q$30,ROW($Q$30:$Q$999 9)-MIN(ROW($Q$30:$Q$9999)),0))))

[p45cal]

Not 100% sure what you're after but test this in AH6 copied down and across:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(AH$30,ROW(AH$30:AH$99924)-ROW(AH$30),0)),--($Q$30:$Q$99924=$Y6))

Also test 103 instead of 3.

[plasteredric]

Not 100% sure what you're after but test this in AH6 copied down and across:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(AH$30,ROW(AH$30:AH$99924)-ROW(AH$30),0)),--($Q$30:$Q$99924=$Y6))

Also test 103 instead of 3.

Thanks, works a treat

[mdmackillop]

While I have no doubt the solution works, I would not regard testing on 3 unique items a real "test". What is the issue in providing a realistic data sample? Personally, I'm not going to spend my time filling in random data for testing results.