Sum Total for rows with Hidden Rows

[Oceans]

I have a filter to To filter out some data

Now with the current result showing i need to get the Sum Total of the rows.

Getting an error, I am guessing because it needs to count for the hidden rows ?

[alansidman]

Suggest you post your worksheet so we can see what the issues are.

[Oceans]

With the

=SUBTOTAL(3,B58:B1225)
&
=COUNTA($B$58:$B$1223)
Functions,

I am getting what I want now.

However, this Function
=COUNTIF(B1183:B1223,"[email protected]")

Did not return the accurate count, not too sure why.

i have a filter for the column to filter out any other emails

[ron2k_1]

Try
=sumproduct(--(B1183:B1223="[email protected]"))

[ron2k_1]

Sorry, I think I know what you're trying to say. Maybe this
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B1183:B1223,ROW(B1183:B1223)-ROW(B1183),0,1)))

[Oceans]

@ ron2k_1

Still giving same wrong count.

I think it is still counting other cells that even does not have the same value of [email protected]


As I changed to
=sumproduct(--(B1221:B1223="[email protected]")) the count now is 3 which is accurate

[ron2k_1]

@ ron2k_1

Still giving same wrong count.

I think it is still counting other cells that even does not have the same value of [email protected]


As I changed to
=sumproduct(--(B1221:B1223="[email protected]")) the count now is 3 which is accurate

It's not the formula is inaccurate. What happens that is the formula is counting how many cells in range B1183:B1223 have the criteria you have stated, and it's returning an accurate number, however, you are not seeing them because you have hidden them by way filtering other columns. So maybe my second formula will work with hidden rows disregarded. Try it and let us know

[ron2k_1]

Oops sorry, I forget to tell you that you need to specify the criteria, here is what I was trying to give you
=SUMPRODUCT(--(B1183:B1223="[email protected]"),SUBTOTAL(3,OFFSET(B1183:B1223,ROW(B1183:B1223)-ROW(B1183),0,1)))

[Oceans]

=SUMPRODUCT(--(B1183:B1223="[email protected]"),SUBTOTAL(3,OFFSET(B1183:B1223,ROW(B1183:B1223)-ROW(B1183),0,1)))

that worked well.

But way too complicated for me

[Oceans]

This one works fine Too
'=SUBTOTAL(3,B58:B1225)

[Oceans]

Still would love to resolve the Mystery

Why would

this Function
=COUNTIF(B1183:B1223,"[email protected]")

would not give the accurate count, as the hidden / filtered cells, do not have the same value !

[ron2k_1]

Upload a sample of your file and explain to us what is the desired result.

[Oceans]

=COUNTIF(B1183:B1223,"[email protected]")

Came back to work, test it before i upload it.


Amazing !, it works !