Filtering Data

[spookykid]

I created these formulas on a separate sheet and I need help with changing it so when the source sheet is filtered it only shows what is filtered and not all the data, any help would be greatly appreciated!

=SUMIFS(OT_COMP!I2:I1048576,OT_COMP!B2:B1048576,"OT",OT_COMP!E2:E1048576,"Place Holder")

=SUMIFS(OT_COMP!I2:I1048576,OT_COMP!B2:B1048576,"OT",OT_COMP!E2:E1048576,"Place Holder",OT_COMP!C2:C1048576,"180")

 

[pgc01]

Re: Help With Filtering Data

Hi
Welcome to the board

For the first one:

=SUMPRODUCT(--(OT_COMP!B2:B1048576="OT"),--(OT_COMP!E2:E1048576="Place Holder"),SUBTOTAL(9,OFFSET(OT_COMP!I2,ROW(OT_COMP!I2:I1048576)-ROW(OT_COMP!I2),)))

Test and adapt for the other.

 

[DanteAmor]

Re: Help With Filtering Data

How about

=SUMPRODUCT(SUBTOTAL(9,OFFSET(OT_COMP!$I$2,ROW(OT_COMP!$I$2:$I$1000)-ROW(OT_COMP!$I$2),,1))*(OT_COMP!$B$2:$B$1000="OT")*(OT_COMP!$E$2:E1000="Place Holder"))

And

=SUMPRODUCT(SUBTOTAL(9,OFFSET(OT_COMP!$I$2,ROW(OT_COMP!$I$2:$I$1000)-ROW(OT_COMP!$I$2),,1))*(OT_COMP!$B$2:$B$1000="OT")*(OT_COMP!$E$2:E1000="Place Holder")*(OT_COMP!$C$2:$C$1000="180"))

 

[spookykid]

Re: Help With Filtering Data

I could get that one to work, but not the part adding the 180 to it, not sure where to actually put it in the formula, tried to put it before the subtotal, but not sure if I did it right or not.

 

[DanteAmor]

Re: Help With Filtering Data

I could get that one to work, but not the part adding the 180 to it, not sure where to actually put it in the formula, tried to put it before the subtotal, but not sure if I did it right or not.

You tried it with the second formula of post#3

 

[spookykid]

Re: Help With Filtering Data

yeah neither of the ones with the number in them would pull the info right, just returned a 0

 

[pgc01]

Re: Help With Filtering Data

Hi

For the second one you just need to add a new condition:

=SUMPRODUCT(--(OT_COMP!B2:B1048576="OT"),--(OT_COMP!E2:E1048576="Place Holder"),--(OT_COMP!C2:C1048576=180),SUBTOTAL(9,OFFSET(OT_COMP!I2,ROW(OT_COMP!I2:I1048576)-ROW(OT_COMP!I2),)))

Remark: if the 180 is a text value use instead

...,--(OT_COMP!C2:C1048576="180"),...

 

[pgc01]

Re: Help With Filtering Data

P. S.

In this case my formulas and Dante Amor formulas are equivalent.

 

[DanteAmor]

Re: Help With Filtering Data

P. S.

In this case my formulas and Dante Amor formulas are equivalent.

Totally agree

 

[spookykid]

Re: Help With Filtering Data

=SUMPRODUCT(SUBTOTAL(9,OFFSET(OT_COMP!$I$2,ROW(OT_COMP!$I$2:$I$8000)-ROW(OT_COMP!$I$2),,1))*(OT_COMP!$B$2:$B$8000="OT")*(OT_COMP!$E$2:E8000="Place Holder")*(OT_COMP!C2:C8000=180))

How would I exclude 180? I tried "<>180" but it doesn't work