# help with subtotal matrix for SUMIF on autofilter

#### rhawke

##### New Member
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Colin Legg

##### MrExcel MVP
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...

#### Colin Legg

##### MrExcel MVP
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:

#### rhawke

##### New Member
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 ...

#### Colin Legg

##### MrExcel MVP
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...

#### rhawke

##### New Member
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

#### rhawke

##### New Member
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?

#### Colin Legg

##### MrExcel MVP
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

Replies
0
Views
354
Replies
0
Views
355
Replies
3
Views
554
Replies
0
Views
315
Replies
17
Views
2K

1,191,534
Messages
5,987,142
Members
440,082
Latest member
belodelokelo

### 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?

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