Excel - How to Use a SUMIF Formula - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.