Question description:
This user has given permission to use the problem statement for this
blog.
hi, i have this formula =SUMPRODUCT((B4:B464<>"")/COUNTIF(B4:B464,B4:B464&"")), i want to add a subtotal to it. so when I filter the column it also sums up the unique number if enteries i have filtered on.
Solved by F. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
28/03/2018 - 02:07
Hi
User
28/03/2018 - 02:07
hi
Excelchat Expert
28/03/2018 - 02:08
I went through your question
Excelchat Expert
28/03/2018 - 02:08
So do you want to apply subtotal for group of entries where the present formula is applied?
Excelchat Expert
28/03/2018 - 02:08
Am I correct?
User
28/03/2018 - 02:09
yes that is correct
Excelchat Expert
28/03/2018 - 02:09
Ok...
Excelchat Expert
28/03/2018 - 02:10
Just put subtotal on the group of cells with 109 as input in total row at the end.
Excelchat Expert
28/03/2018 - 02:11
Whenever you will filter the displayed sums will be totalled
User
28/03/2018 - 02:11
No i dont think you understand
User
28/03/2018 - 02:12
want to add another formula to the one i have already
User
28/03/2018 - 02:12
i do not want a seperate one
Excelchat Expert
28/03/2018 - 02:12
Ok
User
28/03/2018 - 02:12
i know how to do a a normal subtotal
Excelchat Expert
28/03/2018 - 02:12
There even use subtotal in beginning. Put 9 in first column & then your formula for second
User
28/03/2018 - 02:12
just want to add to the exisiting formula i have stated please
Excelchat Expert
28/03/2018 - 02:12
Ok.
User
28/03/2018 - 02:13
can you copy and paste my formula i have given you and add what i need to do so i can see pls
Excelchat Expert
28/03/2018 - 02:13
Sure
User
28/03/2018 - 02:13
thx
User
28/03/2018 - 02:15
by the way my formula is looking on text and vaules, not just values
Excelchat Expert
28/03/2018 - 02:15
Ok
User
28/03/2018 - 02:16
given you an example
User
28/03/2018 - 02:21
is it not working?
Excelchat Expert
28/03/2018 - 02:21
I have copied to my local excel..Google Spreadsheet sometimes behave weird
Excelchat Expert
28/03/2018 - 02:21
Give me few minutes
User
28/03/2018 - 02:21
ok
User
28/03/2018 - 02:22
do you need me to clarify again what i need or you understand?
Excelchat Expert
28/03/2018 - 02:22
I got it.. You wish to apply this formula for filtered cells
Excelchat Expert
28/03/2018 - 02:22
Not on all.. Right
Excelchat Expert
28/03/2018 - 02:23
Just be selecting few values, the formula should calculate only for visible cells'
Excelchat Expert
28/03/2018 - 02:23
Did I understand correct?
User
28/03/2018 - 02:23
yes, so for wexample, the data i have just pasted to you, if you were to filter on 27/03 it will only subtotal 1
User
28/03/2018 - 02:23
the unique visable cells
Excelchat Expert
28/03/2018 - 02:24
Ok
User
28/03/2018 - 02:24
so if there are duplicates in the filter it will calculate on the unique reference in the cells
Excelchat Expert
28/03/2018 - 02:24
Ok.. Thats what your applied formula is doing
User
28/03/2018 - 02:24
yes that is correct
Excelchat Expert
28/03/2018 - 02:24
Just not working on visible cells only
User
28/03/2018 - 02:24
i just want a subtotal added to is
User
28/03/2018 - 02:24
it
Excelchat Expert
28/03/2018 - 02:25
Ok
Excelchat Expert
28/03/2018 - 02:25
king on it
Excelchat Expert
28/03/2018 - 02:25
I am working on it
User
28/03/2018 - 02:25
thx
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.