Apply the SUMPRODUCT formula to filtered cells

[drom]

hi and thanks in advance!

I am using a SUMPRODUCT formula with a few conditions (works Fine)

Now I would like to apply to the same SUMPRODUCT formula, a Subtotal(9;Table1[HoursPlus])

So instead of:

• =SUMPRODUCT(--(Table1[Employee]<>"");--(Table1[Hours]<>"");--(Table1[Hours]<=1+(Table1[Hours]>0));Table1[Hours])

I am looking for something like:

• =SUMPRODUCT(--(Table1[Employee]<>"");--(Table1[Hours]<>"");--(Table1[Hours]<=1+(Table1[Hours]>0));--(SUBTOTAL(9;rcHorasExtrasShtHorasExtras)))

So Apply the SUMPRODUCT formula to filtered cells

Any IDEA ??

 

[awoohaw]

my guess is this, entered as an Array Formula with CNTL-SHFT-ENTR (CSE) keystroke. But, I unsure how the SUBTOTAL value will play out as that is a single value and not an array.

=SUM((Table1[Employee]<>"")*(Table1[Hours]<>"")*(Table1[Hours]<=1+(Table1[Hours]>0))*(AGGREGATE(9;5;rcHorasExtrasShtHorasExtras)))

if that does work syntax wise, all I have done is
1. make the entire formula a SUM.
2. instead of delimiting the arrays with semi-colon I am multiplying them,
and
3. changed the SUBTOTAL formula to an AGGREGATE Function that ignores hidden rows

[INDENT=2]=AGGREGATE(9;5;rcHorasExtrasShtHorasExtras)[/INDENT]

 

[drom]

If I apply before the first -- inside the Sumproduct

• --(SUBTOTAL(9;OFFSET(rcSUM;ROW(rcSUM)-MIN(ROW(rcSUM));0;1)));
• I mean apply this to the column I am trying to sum, and then take it out from the last sum product item

I get the job Done, kind of (If I have copied OK, the formula):

=SUMPRODUCT(--(SUBTOTAL(9;OFFSET(rcSUM;ROW(rcSUM)-MIN(ROW(rcSUM));0;1)));--(rcEmployee<>"");--(rcSUM<>"");--(rcSUM<=1+(rcSUM>0))))

Thanks !

 

[awoohaw]

Happy you figured a solution out.
Best wishes!