help with subtotal matrix for SUMIF on autofilter

rhawke

New Member
Joined
Mar 25, 2009
Messages
4
Hi guys!

I would like to do something, what I think is basic, but it looks like Excel disagrees. I already red all the posts on the "HOW TO get SUMIF with autofilters to work", but to be honest all the OFFSET, ROW, COLUMN, MIN, MAX just confuse me even more.

So here my question that I hope, somebody can answer within seconds:

I have a list and filter it with Autofilter. In this list there is one Column that contains positive as well as negative Values. Now i would like to have a Summary-Part at the top showing me how many positive numbers are in the list and how many negative.

without the Autofilter this works:

Code:
=SUMIF(D17:D1000;">0")
=SUMIF(D17:D1000;"<0")
So basically i want the same function, just to consider the autofilter.

I would be very thankful for a hint. I have not gotten anywhere during the last 2 hours ...

Best Regards,

Robert
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi,

Try these:

Code:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(D17,ROW(D17:D1000)
    -ROW(D17),,1)),--(D17:D1000 > 0))

Code:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(D17,ROW(D17:D1000)
    -ROW(D17),,1)),--(D17:D1000 < 0))

Hope that helps...
 
Upvote 0
Hi,

Sorry, I just realised that I'm confused by this line in your post:
Now i would like to have a Summary-Part at the top showing me how many positive numbers are in the list and how many negative.
Do you want to sum them, or count them?

If you want to count them, use the formulas in the previous post. If you wanted to sum them then use these:
Code:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(D17,ROW(D17:D1000)
    -ROW(D17),,1)),--(D17:D1000 > 0))
Code:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(D17,ROW(D17:D1000)
    -ROW(D17),,1)),--(D17:D1000 < 0))

Hope that helps...
 
Last edited:
Upvote 0
You had to get confused ... sorry for that.

I wanted the Sum and not the Amount. So thanx alot!

BTW: How the heck do you come up with something like that? Is there a good website explaining these things in detail? I already use a lot of SUM() Matrix functions for adding numbers based on several criteria and thought that I understood the concept. But after seeing this i now believe I only understood a mini-part of it ...
 
Upvote 0
Hi,

Glad to help. This one's quite a complicated formula but Laurent Longre / **** Kusleika explain it here:

http://www.dailydoseofexcel.com/archives/2005/05/11/arrays-with-offset/

The key is that the OFFSET() function returns an array of ranges when an array is passed into its ROWS (or COLS) argument.

Also, if you're looking for a more general array formula tutorial, I think google should show some good hits.

Hope that helps...
 
Upvote 0
Hi Colin!

Just read through that link but to be honest, it's not really clear to me. I tried to apply your formula to something similar, but unfortunately did not succeed. So if this is only a few seconds for you I'd be happy if you could help me out once more:

I would need this Formula to use only filtered cells:

Code:
{=SUM((O19:O65536="")*(A19:A65536<>""))}
What is is supposed to do:

Code:
A                              O
Titles                      Checkboxes
 
Titel 1                         x
Titel 2                         x
Titel 3
Titel 4
This functions checks how many lines there are where column A contains an entry but column O does not -> how many titles are not checked.

So how can I get this into a SUBTOTAL formula again?

Robert
 
Upvote 0
oh yeah ... tried around a little bit more and finally got it :D

Code:
=SUMPRODUCT(SUBTOTAL(3;OFFSET(J19;ROW(J19:J65536)-ROW(J19);;1));--(G19:G65536 <> "")*(O19:O65536 = ""))
So basically all I have to change is the part after the -- and the subtotal function. The rest stays the same, and I don't have to know what it means exactly :D

it works, but the worksheet is getting awfully slow. I guess this is cause I use the formula all the way down to 65 thousand.

What is your advise for that? Get the last cell and use the INDIRECT function to make the array formulas only go as far as they have to?

Or should I try to avoid the array formulas and try to use the regular SUBTOTAL(3; several times and subtract values?
 
Upvote 0
Hi Robert,

This one is a count.

For the subtotal function, passing in an argument 3 equates to COUNTA on the visible cells. COUNTA counts the cells which are not empty.

So, we can kill 2 birds with one stone - we can use Subtotal to check for visible rows and Titles which are not empty, ie. this will cover this condition in your current formula: (A19:A65536<>"").

So the answer is:
Code:
=SUMPRODUCT(
    SUBTOTAL(3,OFFSET(A19,ROW(A19:A65536) -ROW(A19),,1)),
    --(O19:O65536=""))

Hope that helps
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top