Sumif only visible cells

[SUMIF]

Hello

I’ve been having some trouble in terms of using sumif only on the visible cells.
My data is currently laid out as follows:

Excel.PNG

What I'm trying to do is:
1) Filter column A so that only the blank cells are visible...

Excel1.PNG

2) Once the list has been filtered i need to use a sumif on the filtered range only & ensure that the value is populated in column A.
3) Any zero values in column A should lead onto the corresponding rows being deleted.



Unfortunately my formula carries out the sumif for everything in a range regardless of whether or not it is filtered.
This is just an extract of what I’m using:

Any ideas on what i can do to amend the code?

I understand some parts but i probably haven't mastered all of the basics (yet!)

Thanks

[nigelbloomy]

Would something like this help?

Please Login or Register  to view this content.

The 9 in Aggregate tells it to sum the list. The 5 tells it to ignore hidden rows.

[Richard Buttrey]

Hi,

Have you tried the

Please Login or Register  to view this content.

function.

[nigelbloomy]

I believe it is SUBTOTAL(109,Range) that ignores hidden values. That one may be helpful as well.

[SUMIF]

Hello all

Wow, thanks for the very quick replies.

nigelbloomy - I don't think the Aggregate formula would work a i need to split the summed amounts out instead of summing all of it (e.g. cell A8 should show a zero value after all of the filtered rows with Apples have been summed, D8+D13=0)

Richard Buttrey - I've not tried subtotal as i think it would just subtotal all of the visible cells in the same way as the aggregate suggestion from nigelbloomy .

I'm not sure i even need to use a filter & might be able to find a workaround using a temporary helper column but it just seems like a much more convoluted way of doing it.

Hopefully you might know of a more elegant approach?

[YasserKhalil]

@ SUMIF
Can you attach a sample of your workbook ?

[SUMIF]

Hi YasserKhalil

Sure, let me know if i you need anything else.

Thanks

[Richard Buttrey]

Hello all



Richard Buttrey - I've not tried subtotal as i think it would just subtotal all of the visible cells in the same way as the aggregate suggestion from nigelbloomy .


Hopefully you might know of a more elegant approach?

Your original request was asking for the sum of all the visible cells so I'm now confused since that's precisely what the SUBTOTAL function will give you. Please explain. Preferably upload a workbook in which you have manually added the total you want so that we can understand the request in context.

[SUMIF]

Hi Richard

Apologies for the confusion.

My original request was to use the SUMIF function for of all the visible cells & not just the SUM function of the visible cells. I think the SUBTOTAL function probably totals in the same was as the SUM function but if there was a SUMIF version of the SUBTOTAL function that would probably work.

As requested i've uploaded a workbook with the cells manually added.

Hopefully this helps clarify what i'm trying to do but let me know if not.

Thanks

[Richard Buttrey]

Hi,

SUMIF is designed to work with ALL cells whether visible or not.

SUBTOTAL is what you want either with the 9, or 109 parameter depending on exactly what you want. 109 excludes manually hidden rows but both will exclude values in hidden filtered rows.

[mangesh.mehendale]

As per my interpretation do you want following formula...

This formula will not work in col C due to circular ref...

Please Login or Register  to view this content.

[SUMIF]

Thanks Richard

Happy to give SUBTOTAL a go but i can't imagine how it could work in a similar way to SUMIF. I need to ensure it would:

1) only total the filtered rows &
2) only total the rows with the same (G/L Account) value in column B.

Perhaps i should do away with the filter & just use some sore of IF statement? The filter is used to bring up the rows with blank cells in column A that's all.

I'm not sure of the best way to take this forward...

[SUMIF]

As per my interpretation do you want following formula...

This formula will not work in col C due to circular ref...

Please Login or Register  to view this content.

Hi Mangesh

Yes this is closer to what i'm looking for. I've just tested this however & unfortunately there is a problem (i don't think the SUMIF is calculating correctly).

I've attached a workbook where I've changed the value in cell D18 from 5 to 3 (it's in yellow). Your formula is in column G & I've highlighted in red the incorrect value of zero (G18). The correct value should be -2 (cell I18).

Thanks Mangesh

[mangesh.mehendale]

Ohh sorry...

Please Login or Register  to view this content.

[SUMIF]

Thanks for this Mangesh

The only other problem is that the SUMIF still sums those values which are not blank.

You can see this happening if you filter in column B for the values 222222.

The SUMIF value is picking up the values for all 3 rows with a 222222 despite only two rows with a blank value in column A.
Cells A13 & A16 should have a value of zero 0=2+(-2) but instead it would have 6=2+(-2)+6.


See image (your formula is in column G:

Query3.PNG

[mangesh.mehendale]

Quite hard....

[SUMIF]

I agree

[Kyle123]

Can't you just use a pivot table?

[SUMIF]

Hi Kyle123
I think a pivot table would work but i'm not sure how to use it with VBA as i've never done it before (bad excuse i know!). It's an interesting way to do it so i might have a look for some previously written pivot table code (unless you're able help).

I have thought of an alternative which requires the use of 4 helper columns so i may/may not go with that unless someone knows of a better way. The cells with "TRUE" in column H need to be deleted (the yellow cells show the formula for that column).

I'll put the code in, test it & upload it on here for others.
Query4.PNG

[mangesh.mehendale]

one easy way

Sum by color... first paste following code into workbook, second filter cell and give different background color, third take a sumbycolor with formula..

Please Login or Register  to view this content.

[mangesh.mehendale]

Also note that you can use above formula in your other vb code with .value = .value and then you can remove the filter and background colors..

[SUMIF]

Thanks Mangesh, i appreciate your help with this.

[karedog]

Hi,

Using formula :
Formula on cell E6 (and to copy down) :

Please Login or Register  to view this content.

Formula on cell C6 (and to copy down) :

Please Login or Register  to view this content.

Using macro :

Please Login or Register  to view this content.

Regards