surbannomad
New Member
- Joined
- Oct 5, 2020
- Messages
- 4
- Office Version
-
- 2019
- Platform
-
- Windows
Hi Everyone,
Learnt so much from the site and the advice you guys give..but cound'nt find an answer to a problem that I am facing....here goes
I have a column numbers of staff. I am currently using
=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(3,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))
=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(103,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))
which work well and produce the counts figures that I require using the multiple criteria...no problem showing the hidden and visisble, and visible only counts. Problem I am facing is that I need to do average staff per range on the same column and median staff per range. In case of averages I have used:
=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(1,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))
=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(101,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))
but I am not getting any values back only getting #DIV appearing in cells. Need your help on this formuale
For Median I am informed by your site that I can use subtotals as per range.... any suggestions for work around....still using subtotal inside statement as I need to difefrentiate from visible and hidden + visible your guidance and help would be gratefully appreciated.......
Learnt so much from the site and the advice you guys give..but cound'nt find an answer to a problem that I am facing....here goes
I have a column numbers of staff. I am currently using
=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(3,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))
=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(103,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))
which work well and produce the counts figures that I require using the multiple criteria...no problem showing the hidden and visisble, and visible only counts. Problem I am facing is that I need to do average staff per range on the same column and median staff per range. In case of averages I have used:
=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(1,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))
=SUMPRODUCT(('SE A+B + CODED'!$AP:$AP>=1)*('SE A+B + CODED'!$AP:$AP<=5)*(SUBTOTAL(101,OFFSET('SE A+B + CODED'!$AP:$AP,ROW('SE A+B + CODED'!$AP:$AP)-MIN(ROW('SE A+B + CODED'!$AP:$AP)),0,1))))
but I am not getting any values back only getting #DIV appearing in cells. Need your help on this formuale
For Median I am informed by your site that I can use subtotals as per range.... any suggestions for work around....still using subtotal inside statement as I need to difefrentiate from visible and hidden + visible your guidance and help would be gratefully appreciated.......