How to subtotal only if certain conditions apply

[RunwayRiga]

Hi,

I found similar questions online but none of the answers worked for me.

I need in BD to make an average of the values in color BB7:BB10000, but only if 2 conditions are met (in this case, if the "route" in AP is the same, and if the "week" in AT is the same).

Normally, I would do like this (in row 7):

=AVERAGEIFS($BB$7:$BB$10000;$AP$7:$AP$10000;AP7;$AT$7:$AT$10000;AT7) and drag the formula all the way down.

Of course Average Ifs would work perfectly on a static array of numbers, but I need the calculation to adapt to the filtering, so I need to use subtotal somehow.

I found this formula but I wasn't able to make it work, probably because I do not know how to apply it properly

{=AVERAGE(IF(SUBTOTAL(2,OFFSET(B2,ROW(B2:B100)-ROW(B2),0)),IF(A2:A100="x",B2:B100)))}

Any suggestions?

Thank you

Giovanni

[Kaper]

Hi Giovanni,

Try to start your array formula with:

Please Login or Register  to view this content.

Anyway ... it woulkd be easier to answer, if you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.View Pic

[RunwayRiga]

Hi Kaper,

so, I rephrase it:

in F2:F15 I have this pretty straightforward formula:

=AVERAGEIFS($E$2:$E$15;$A$2:$A$15;C4;$B$2:$B$15;D4)

It calculates the average of all the values in col E on certain conditions (for all those values for which column C matches column A, and for which column D matches column B)

I want this formula to change its output based on how I filter the data, as it happens when I use subtotal.

As you can see in the example, if I apply a filter, the average doesn't make sense anymore, as it still applies to the old array of numbers.

On the other hand, if I would use subtotal I would not be allowed to set any condition.

How can I do it?

Thank you!

Giovanni

[Kaper]

I'd add supplementary column with

Please Login or Register  to view this content.

(see G2 in attachment)
and then main forrmula (F2 and down):

Please Login or Register  to view this content.

At the moment I see no simple way to achieve this without this helper column.

Note that F9 result is #DIV/0! error (not 2 as you suggested in H19), because after filtering in column E, there is no visible data, where columns A and B are 2 and 8 resp.

See attachment

[RunwayRiga]

Thank you, I managed to solve it myself

=SUMPRODUCT(SUBTOTAL(3;OFFSET($BC$7:$BC$8902;ROW($BC$7:$BC$8902)-ROW($BC$7);0;1));--($BC$7:$BC$8902=BC7);($BB$7:$BB$8902)) / SUMPRODUCT(SUBTOTAL(3;OFFSET($BC$7:$BC$8902;ROW($BC$7:$BC$8902)-ROW($BC$7);0;1));--($BC$7:$BC$8902=BC7))

where in BC I have the data to compare and in BB I have the data to sum up (first part of the formula) and to count (second part of the formula). Dividing the two I get to the average I wanted.

This formula sums up all data in BB that have the same value in BC, then it counts them so that dividing the two I get to the average. And it uses subtotal, so it recalculates when I filter the database.

Giovanni

[Kaper]

Going that path, you could skip third argument in first sumproduct by using subtotal(9->"sum" instead of subtotal(3->"count":

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

if (as in the opening post) there are two conditions (let's say second is in BD column) the approach to convert boolean values to 1/0 with multiplication could be especially usefull:

Please Login or Register  to view this content.

[RunwayRiga]

Thank you Kaper,

good points! I will see which solution is faster, as this formula uses an awful lot of resources.

Giovanni

[Jonmo1]

as this formula uses an awful lot of resources.

All the more reason to go with the helper column solution suggested in Post #4
Helper columns are not a bad thing, that's why they have the word 'Help' in their name.
Excel gives you thousands of columns, might as well use a couple of them.


Also, once you have 1 formula that you want to be dependant on visible cells only
It's very likely you'll then have another, then another....
Each additional formula you create using the array version is duplicating the work of the subtotal.

If you use the helper column method, the subtotal bit is only done once, and each subsequent formula then refers to that column.

MUCH more efficient.

[GeneralDisarray]

=SUMPRODUCT(SUBTOTAL(9;OFFSET($...

Dumb question - how are you using semi colons to separate arguments of a function? Is that a regional setting?

[Tony Valko]

Dumb question - how are you using semi colons to separate arguments of a function? Is that a regional setting?

Yes, some international locations use semi-colons as the argument separator.

[RunwayRiga]

Norway, to be precise.

[RunwayRiga]

All the more reason to go with the helper column solution suggested in Post #4
Helper columns are not a bad thing, that's why they have the word 'Help' in their name.
Excel gives you thousands of columns, might as well use a couple of them.


Also, once you have 1 formula that you want to be dependant on visible cells only
It's very likely you'll then have another, then another....
Each additional formula you create using the array version is duplicating the work of the subtotal.

If you use the helper column method, the subtotal bit is only done once, and each subsequent formula then refers to that column.

MUCH more efficient.

Yes, I tried it now (the solution with the dummy subtotal, by the very helpful Kaper) and it works great! Thanks

I was not using it at the beginning because
1 - I am stubborn
2 - I hadn't fully understood it, but now I think I have:

I think that, when counting a column with subtotal, in particular a column that is not visible, the result is 0, right? That is why, if you add in the conditions that the result of the subtotal(2;xx) must be 1, you immediately cut out all the rows that are not visible. I hope this is the explanation)

Giovanni

[Jonmo1]

You're welcome.

I think that, when counting a column with subtotal, in particular a column that is not visible, the result is 0, right? That is why, if you add in the conditions that the result of the subtotal(2;xx) must be 1, you immediately cut out all the rows that are not visible. I hope this is the explanation)

Yes, that's exactly how it works, except it's for visible rows, not columns...