Help with SUMIFS incorporating SUBTOTAL

[ddub25]

I have the formula below, but I'd like to incorporate the 2nd formula (SUBTOTAL) also.

=SUMIFS(M13:M288,B13:B288,"Y")

=SUBTOTAL(9,M13:M288)

I have tried this:
=SUMIFS(SUBTOTAL(9,M13:M288),B13:B288,"Y")

and this:
=SUMIFS((SUBTOTAL(9,M13:M288)),B13:B288,"Y")

but the syntax seems to be wrong.

Can anyone help. Thanks.

Dan

 

[barry houdini]

Hello Dan, just to confirm.....

You want to sum M13:M288 when the corresponding cell in B13:B288 is equal to "Y"......but only for visible rows after filtering?

If so try this version

=SUMPRODUCT(SUBTOTAL(9,OFFSET(M13,ROW(M13:M288)-ROW(M13),0)),(B13:B288="Y")+0)

 

[Yahya]

Try This

=SUMPRODUCT(SUBTOTAL(9,OFFSET(M13:M288,ROW(M13:M288)-ROW(M13),0,1,1)),--(B13:B288="Y"))

 

[ddub25]

thanks very much Yahya, very useful.

Dan

 

[rangu]

Hi Yahya,

Thanks , similarly please suggest the formula for two different criteria

 

[barry houdini]

Do you mean an additional criterion in a new column? You can add more like this

=SUMPRODUCT(SUBTOTAL(9,OFFSET(M13,ROW(M13:M288)-ROW(M13),0)),(B13:B288="Y")+0,(Z13:Z288="X")+0)

 

[rangu]

Thank you Barry, it must work i will fix it