Question description:
This user has given permission to use the problem statement for this
blog.
How can I do a sumif formula on visible cells only
Solved by B. B. in 56 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/06/2018 - 10:50
Hi!
Excelchat Expert
08/06/2018 - 10:51
Do you create an example of your task for me?
User
08/06/2018 - 10:53
Hi
User
08/06/2018 - 10:53
I want to do a sumif formula on visible cells only
Excelchat Expert
08/06/2018 - 10:53
To do this, you need to combine the sumproduct and offset formulas.
User
08/06/2018 - 10:54
so I want to know how many products have greater than 200 DOI
User
08/06/2018 - 10:54
and then if filtered on EAST that number should change
Excelchat Expert
08/06/2018 - 10:55
Please, give me a few minute to write a formula for your example, ok?
User
08/06/2018 - 10:55
so my answer should be 2
User
08/06/2018 - 10:55
ok
User
08/06/2018 - 11:00
how is it going?
Excelchat Expert
08/06/2018 - 11:01
Almost done :)
User
08/06/2018 - 11:04
great
User
08/06/2018 - 11:06
only couple of minutes remaining on the session
Excelchat Expert
08/06/2018 - 11:07
I am asking for an extension. I can not include the East condition.
User
08/06/2018 - 11:08
yes this is a tricky one as I am not sure if it is possible to do sumif on visible cells only
Excelchat Expert
08/06/2018 - 11:08
ok, It's done
User
08/06/2018 - 11:09
are you able to put that in English?
Excelchat Expert
08/06/2018 - 11:12
Error when copying
Excelchat Expert
08/06/2018 - 11:13
I'm trying to fix it quickly.
User
08/06/2018 - 11:16
if the formula was then put onto Product - is it possible to make the formula still do a sumif of DOI greater than 200
User
08/06/2018 - 11:17
so the rather than looking up vs cell D2, instead it should just sumif visible cells greater than 200 when filter is applied
Excelchat Expert
08/06/2018 - 11:18
Try to open this file and copy the formula from D2.
Excelchat Expert
08/06/2018 - 11:18
[Uploaded an Excel file]
Excelchat Expert
08/06/2018 - 11:19
Strange that it shows error when copying.
User
08/06/2018 - 11:19
yes but is it possible to build into the filter a function to just do sumif on visible cells
User
08/06/2018 - 11:19
whether it is by area or product
User
08/06/2018 - 11:20
so if the filter is on both easy and galaxy then the visible cells will show 1
Excelchat Expert
08/06/2018 - 11:22
I add this condition and send you a file
User
08/06/2018 - 11:22
so if you look at the changes
User
08/06/2018 - 11:22
at the top I want the value of all filtered cells where DOI is greater than 200 but this will apply when there are multiple filters
Excelchat Expert
08/06/2018 - 11:22
[Uploaded an Excel file]
Excelchat Expert
08/06/2018 - 11:23
This formula counts only for the condition in C2 and for those that are visible (eg Galaxy).
User
08/06/2018 - 11:23
please can you change the layout of the file to how I have shown on the spreadsheet
User
08/06/2018 - 11:24
But I want no condition in cell C2
Excelchat Expert
08/06/2018 - 11:24
Ok, I can
User
08/06/2018 - 11:26
So if someone filters on Mars - it will give me 3
Excelchat Expert
08/06/2018 - 11:26
[Uploaded an Excel file]
User
08/06/2018 - 11:26
but if someone filters on east and mars it will give 1
Excelchat Expert
08/06/2018 - 11:27
Look to the file. Everything works as you wrote. The number dynamically changes when filtering.
User
08/06/2018 - 11:27
yes this is great
User
08/06/2018 - 11:27
I am just going to apply that to my file and will see if it works
User
08/06/2018 - 11:28
give me one minute
Excelchat Expert
08/06/2018 - 11:28
Ok!
Excelchat Expert
08/06/2018 - 11:30
If you want to change the value '200' to another, in the formula replace this value.
User
08/06/2018 - 11:30
its not working when I change my file
User
08/06/2018 - 11:30
what if I want to say if DOI =0
Excelchat Expert
08/06/2018 - 11:31
Did you set all ranges well?
User
08/06/2018 - 11:31
yes but I am trying to do if DOI = 0
Excelchat Expert
08/06/2018 - 11:32
[Uploaded an Excel file]
Excelchat Expert
08/06/2018 - 11:32
I changed the condition to DOI = 0. It works
Excelchat Expert
08/06/2018 - 11:33
If you want to change the values for DOI, just change it in the last condition: --(F5:F10=0)
User
08/06/2018 - 11:36
yes it works but what if I want the value in the column
Excelchat Expert
08/06/2018 - 11:36
What means 'value' in the column?
User
08/06/2018 - 11:36
so where DOI = 0 I want it to sum all the values
Excelchat Expert
08/06/2018 - 11:37
If you want a sum, not a quantity, then you have to change 2 to 9 at the beginning.
Excelchat Expert
08/06/2018 - 11:38
After subtotal
Excelchat Expert
08/06/2018 - 11:38
Is it working?
User
08/06/2018 - 11:38
no it comes as 0
Excelchat Expert
08/06/2018 - 11:39
because in the condition you check only for the value 0.
Excelchat Expert
08/06/2018 - 11:39
Change the last condition to >200
Excelchat Expert
08/06/2018 - 11:39
--(F5:F10>200)
User
08/06/2018 - 11:39
yes I want it to give me - if DOI = 0 then sum up the values of all visible filtered cells
User
08/06/2018 - 11:40
but that will sum up where DOI is less than 200
Excelchat Expert
08/06/2018 - 11:40
sum from the Value EUR column?
User
08/06/2018 - 11:40
I want it where DOI is exactly 0
User
08/06/2018 - 11:40
yes
User
08/06/2018 - 11:41
sorry wait
User
08/06/2018 - 11:41
let me add in another column
Excelchat Expert
08/06/2018 - 11:41
It changes everything. I hope I can improve my formula.
Excelchat Expert
08/06/2018 - 11:41
until the end of time.
User
08/06/2018 - 11:41
actually I don't need to add in a column
User
08/06/2018 - 11:42
so if I filter on mars in product
User
08/06/2018 - 11:42
I want it to say wherevere DOI = 0 then sum up the value
User
08/06/2018 - 11:42
so answer should be 150
Excelchat Expert
08/06/2018 - 11:43
ok, please change the first part of offset formula to Offset(D5:D10,
Excelchat Expert
08/06/2018 - 11:44
Now it will return the sum for the Value column.
Excelchat Expert
08/06/2018 - 11:45
Is it working?
User
08/06/2018 - 11:45
yes this works
User
08/06/2018 - 11:45
this is great
Excelchat Expert
08/06/2018 - 11:45
super!
User
08/06/2018 - 11:45
please can you send me your final file for reference
User
08/06/2018 - 11:45
so I can use the template in the future
Excelchat Expert
08/06/2018 - 11:45
[Uploaded an Excel file]
Excelchat Expert
08/06/2018 - 11:46
Do you have any more questions?
User
08/06/2018 - 11:46
no thanks
Excelchat Expert
08/06/2018 - 11:46
Thank you and invite you again to the GotIt platform.
User
08/06/2018 - 11:46
many thanks for your help
User
08/06/2018 - 11:46
really useful
Excelchat Expert
08/06/2018 - 11:46
Have a good day! :)
User
08/06/2018 - 11:46
very complex formula for me!
User
08/06/2018 - 11:47
you too
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.
