Hi wizards!
I was hoping one of you bright heads could maybe help me out with a formula I'm struggling with
I am trying to get the average value of all visible (non-filtered) cells in range 'Data for 2023'!$Y$9:$Y$2000 where the corresponding value in column D is not equal to "DC jobs".
This is what I have landed on so far:
=SUMPRODUCT(('Data for 2023'!$D$9:$D$2000<>"DC jobs")*(SUBTOTAL(1;OFFSET('Data for 2023'!$Y$9;ROW('Data for 2023'!$Y$9:$Y$2000)-MIN(ROW('Data for 2023'!$Y$9:$Y$2000));0))))
But it gives me a div error and I can't figure out why because if I change to (SUBTOTAL(2 instead of (SUBTOTAL(1 it will correctly count numbers.
Any help would be greatly appreciated!
//Jay
I was hoping one of you bright heads could maybe help me out with a formula I'm struggling with
I am trying to get the average value of all visible (non-filtered) cells in range 'Data for 2023'!$Y$9:$Y$2000 where the corresponding value in column D is not equal to "DC jobs".
This is what I have landed on so far:
=SUMPRODUCT(('Data for 2023'!$D$9:$D$2000<>"DC jobs")*(SUBTOTAL(1;OFFSET('Data for 2023'!$Y$9;ROW('Data for 2023'!$Y$9:$Y$2000)-MIN(ROW('Data for 2023'!$Y$9:$Y$2000));0))))
But it gives me a div error and I can't figure out why because if I change to (SUBTOTAL(2 instead of (SUBTOTAL(1 it will correctly count numbers.
Any help would be greatly appreciated!
//Jay