sumifs on filtered cells

[trexlim]

Hi,

Can anyone assist me on this function?

current formula:

=ROUNDUP((SUMIFS('MC1 (SG)'!$J:$J,'MC1 (SG)'!$N:$N,'SG MASTER'!$K$1,'MC1 (SG)'!$E:$E,'SG MASTER'!$B14)/6000,0)

thanks in advance!

 

[Aladin Akyurek]

Are you trying to conditionally sum an autofitered range?

 

[trexlim]

Are you trying to conditionally sum an autofitered range?

Yes. my master list will re-calculate (SUMIFS) based on the filtered cells on the data page.

I have read forum saying 'sumifs' doesn't work on filtered cells, is that true?

 

[Aladin Akyurek]

1. In C14 of SG MASTER enter:

=ROUNDUP(SUMPRODUCT(SUBTOTAL(9,OFFSET(INDEX('MC1 (SG)'!$J:$J,1,1),ROW('MC1 (SG)'!$J:$J)-ROW(INDEX('MC1 (SG)'!$J:$J,1,1)),0)),--('MC1 (SG)'!$N:$N=$K$1),--('MC1 (SG)'!$E:$E=$B14))/6000,0)

Note. It's not a good idea to reference whole columns for reasons of efficiency.

2. Alternatively...

In K2 (assuming a header in K1) of MC1 (SG) enter and copy down:

=SUBTOTAL(2,J2)

In C14 of SG MASTER enter:

=ROUNDUP(SUMIFS('MC1 (SG)'!$J:$J,'MC1 (SG)'!$K:$K,1,'MC1 (SG)'!$N:$N,$K$1,'MC1 (SG)'!$E:$E,$B14)/6000,0)​