Application.SumIfs with specialcells (filtered rows)

[JasperD]

Hi guys,

I set a range with values as follows

Please Login or Register  to view this content.

and that works fine

If I do this - I get the result as expected :

Please Login or Register  to view this content.

however, I want to add another criteria, so I try to do this :

Please Login or Register  to view this content.

This gives me a #VALUE in cell E1 and I can't figure out why...

Can anyone please point me in the right direction? I want to avoid looping through the visible rows to keep things speedy :-)
Thanks in advance for any reply!

[AlphaFrog]

If you're filtering, why don't you set the filter on column F for "Pear"?

[JasperD]

Hi AlphaFrog!

To be honest, that's what I'm doing now - I loop through the different filter criteria and then use SUBTOTAL(109) to count the totals.
However, this is rather slow and I was hoping that there's a way to use SUMIFs with filtered rows.
So far I haven't been able to find a way though, but I really hope I'll be able to find something, cause this is getting annoying :-)

[AlphaFrog]

Hi AlphaFrog!

To be honest, that's what I'm doing now - I loop through the different filter criteria and then use SUBTOTAL(109) to count the totals.
However, this is rather slow and I was hoping that there's a way to use SUMIFs with filtered rows.
So far I haven't been able to find a way though, but I really hope I'll be able to find something, cause this is getting annoying :-)

Can you show your whole macro?

[JasperD]

The slow part :

Please Login or Register  to view this content.

It loops this part 17 times, but in the end, it will loop it about 80 times
Working with Application.Subtotal doesn't make it any quicker

[JasperD]

I retract my first comment, using Application.Subtotal(109, .Range(weekcll & "8:" & weekcll & x)) does make it slightly quicker, so I used that instead.

One of the reasons I can think of that might help make it quicker is that I now use x as the last row when the data is not filtered, which is about 32.000
However, I think the filtered data is only a couple of hundred rows each time - is there a way to set x as the last row of FILTERED data?

[AlphaFrog]

That's not the whole code; that's still only part of it. I'm sure you're trying help by keeping things simple, but it would help greatly to be able to see everything; the whole procedure.

I'll bet the real speed bottleneck is not the SUMIFS versus SUBTOTAL function or the last visible cell. So better yet, if you could post an example workbook with data and the entire macro and a sheet with has the expected results and a brief description of what you're doing, I'd be happy to look at where it may be optimized.

If you still just want to do it piecemeal then...
Last used visible row in column weekcol
x = Cells(Rows.Count, weekcol).End(xlUp).Row
I doubt you'll see much speed increase if any with just that change though.

[JasperD]

Hi Alphafrog,

The whole code spans three or four routines and is quite long.
I know this part is making it slow though, cause if I skip it, the data shows up in a jiffy - if I leave it, the data takes ~2 seconds longer to run...
I will see if I can make a workbook with the confidential data removed, but still populated with 30k + rows
In the mean time, I've described my challenge in more depth here : http://www.excelforum.com/excel-prog...nal-array.html
Please have a look if you're not too busy.

Thanks so much!