Average IFS in combination with Subtotal

[chaz6623]

Good day,

I hope to find some help with a formula that is driving me nuts for over a week.

I have a rather large spreadsheet which I need to use Subtotal/Sumproduct to filter our or return results when I filter monthly. I want to find an average of that Subtotal but filtering 2 different columns.

I'd like to average column G while filtering for columns R & S. This the formula I thought I could use but it is not returning the correct figures.

=AVERAGE(IF(SUBTOTAL(2,OFFSET(A1,ROW(G10:G1501)-ROW(G10),0,1)),IF(R10:R1501=0,G10:G1501),IF(S10:S1501=0,G10:G1501)))

any help?

 

[jasonb75]

It might be that some of the data in column A is not numeric and is ignored by subtotal(2.

See if this one gives the correct result.

=AVERAGE(IF(SUBTOTAL(9,OFFSET(G1,ROW(G10:G1501)-ROW(G10),0,1)),IF(R10:R1501=0,G10:G1501),IF(S10:S1501=0,G10:G1501)))

Without knowing the expected result for a data set that is currently returning an incorrect result it is going to be difficult to pinpoint the problem.

 

[chaz6623]

=AVERAGE(IF(SUBTOTAL(9,OFFSET(G1,ROW(G10:G1501)-ROW(G10),0,1)),IF(R10:R1501=0,G10:G1501),IF(S10:S1501=0,G10:G1501)))

Thank you for the reply.

In the example sheet I uploaded here, when filtering Row R & S to "0" - the total on Column G is $3179.24. Diving that by 2 (the filtering only brings 2 results) the average is $1589.62
The formula you provided gave a result of $5.16

I had another formula in another cell that gives the correct average amount BUT it is because I manually filtered the R column.

=AVERAGE(IF(SUBTOTAL(2,OFFSET(G10,ROW(G10:G1502)-ROW(G10),0)),IF(R10:R1502=0,G10:G1502)))

Similarly, when filtering row R to "0" and S to "1" - the total on Column G is $14950.43. Diving that by 2 (the filtering only brings those 2 results) the average is $7475.21
The formula brings a result of $3737.61. - Somehow the formula is dividing by 4 not 2 ($14950.43 / 4 = $3737.61)

 

[jasonb75]

I thought that there would be more data not visible in the screen capture. I've done a test on the values shown and this formula works correcty.

=AVERAGE(IF(SUBTOTAL(9,OFFSET(G10,ROW(G10:G1501)-ROW(G10),0,1)),IF(R10:R1501+S10:S1501=0,G10:G1501)))

 

[chaz6623]

I thought that there would be more data not visible in the screen capture. I've done a test on the values shown and this formula works correcty.

=AVERAGE(IF(SUBTOTAL(9,OFFSET(G10,ROW(G10:G1501)-ROW(G10),0,1)),IF(R10:R1501+S10:S1501=0,G10:G1501)))

Thanks!!!!!! that does work.

What about when R is "0" but S is "1"?

 

[jasonb75]

See if this works,

=AVERAGE(IF(SUBTOTAL(9,OFFSET(G10,ROW(G10:G1501)-ROW(G10),0,1)),IF(((R10:R1501=0)+(S10:S1501=1))=2,G10:G1501)))

 

[chaz6623]

See if this works,

=AVERAGE(IF(SUBTOTAL(9,OFFSET(G10,ROW(G10:G1501)-ROW(G10),0,1)),IF(((R10:R1501=0)+(S10:S1501=1))=2,G10:G1501)))

You sir ARE the MAN!!!!

Thank you so much.

 

[jasonb75]

You are most welcome!

Thanks for the feedback