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 ?
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 ?
Suggest you post your worksheet so we can see what the issues are.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
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
Try
=sumproduct(--(B1183:B1223="[email protected]"))
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
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)))
@ 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
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)))
=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
This one works fine Too
'=SUBTOTAL(3,B58:B1225)
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 !
Upload a sample of your file and explain to us what is the desired result.
=COUNTIF(B1183:B1223,"[email protected]")
Came back to work, test it before i upload it.
Amazing !, it works !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks