**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.*