Subtotal if average formula on visible cells

[JBM91]

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

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would recommend using a helper column with something like

=subtotal(103,a2)

and then you can just use the averageifs function using the helper col as a criteria.

 

[JBM91]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would recommend using a helper column with something like

=subtotal(103,a2)

and then you can just use the averageifs function using the helper col as a criteria.

Hi Fluff,

Thanks for the tip - I have updated my info!

How would you suggest using the helper column as a criteria if you don't mind me asking? (I am terribly sorry if the question is somewhat mundane, I think I might have the excel equivalent of domestic blindness from staring at it for so long).

=AVERAGEIF('Data for 2023'!$D$9:$D$2000;"<>DC jobs";'Data for 2023'!$Y$9:$Y$2000)

 

[Fluff]

If you have the helper column in col Z of the "Data for 2023" sheet you could use

=AVERAGEIFS('Data for 2023'!$Y$9:$Y$2000;'Data for 2023'!$D$9:$D$2000;"<>DC jobs";'Data for 2023'!$Z$9:$Z$2000,1)