combining 2 formulas for sub ifs

[munozc]

I found 2 very helpful formulas that work separately but I'm looking to find a way to combine them.

I want to calculate the subtotal of D2:D101 only if the number is greater than 0 (so no negative amounts) and if the transaction type is "SALE" for G2:G101

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2,ROW(D2:D101)-ROW(D2),0,1)),--(D2:D101>0))

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D101,ROW(D2:D101)-MIN(ROW(D2:D101)),,1)),--(G2:G101="SALE"),D2:D101)

if there is 1 easier way to accomplish this then that would be great too

Can anyone please help me?!

 

[Fluff]

Hi & welcome to MrExcel.
Do you want to ignore hidden rows in your sum?

 

[munozc]

Hi & welcome to MrExcel.
Do you want to ignore hidden rows in your sum?

Yes. I want it to only calculate the rows that display

 

[Fluff]

In that case how about

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2:D101,ROW(D2:D101)-MIN(ROW(D2:D101)),,1)),(G2:G101="SALE")*(D2:D101>0))

 

[munozc]

In that case how about

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2:D101,ROW(D2:D101)-MIN(ROW(D2:D101)),,1)),(G2:G101="SALE")*(D2:D101>0))

YES!!!! THAT WORKED PERFECTLY!!! THANK YOU!

 

[Fluff]

You're welcome & thanks for the feedback.