Sum(If(Text in Range,Range))/a number to average but don’t count na’s

[mjones]

Hi Again,

Here’s my incorrect formula but a start. It gets a total of numbers in B192:B378 if “ngf” is in column V. Then I'm trying to get an average of the numbers that were totaled.

{=(SUM(IF('Zones by Method'!V$192:V$378="ngf",('Zones by Method'!B$192:B$378))))/T$38}

The problem is the last T$38. It's incorrect because it’s a total of how many numbers of interest there should be in range 192:378 but some numbers counted are na and the na’s shouldn’t be added or averaged in.

Instead of dividing by T$38, is there another way to know how many non-na numbers are in B192:B378 when "ngf" is in column V, and then use that number to average the B192:B378 "ngf"'s total?

I hope this makes sense.

Sounds like I need a miracle but I've seen many miracles on this board in the past. Crossing my fingers.

Thank you,

Michele

 

[kweaver]

Please update your profile to indicate which version of Excel you are using.

Have you tried AVERAGEIFS? On a smaller set of data this seems to do what you want:

=AVERAGEIFS(B2:B52,B2:B52,"<>N/A",C2:C52,"ngf")

 

[mjones]

Nope, never tried averageifs. As usual, people on this board never fail to amaze me! Works perfectly! Thanks again!

 

[mjones]

Hi All,

I kindly request a tweak to that last formula.

For =AVERAGEIFS(B2:B52,B2:B52,"<>N/A",C2:C52,"ngf"), I'd like to also include "agf", i.e. average when either ngf or agf in column C so both are included in the average.

P.S. - I've updated my profile. Thanks for the remind. I use 365 Business and Windows 10, which by the way is making a bunch of us crazy because as consultants we want to use 365 Business licenses from several companies so we can access both company's SharePoint files and Teams amongst other things but it seams Microsoft doesn't play nice. We've now abandoned Teams and are trying to determine the best way to share files - we're a worldwide group.

Cheers,

Michele

 

[Fluff]

How about

=AVERAGE(FILTER(B2:B52,(B2:B52<>"N/A")*((C2:C52="ngf")+(C2:C52="agf"))))

 

[mjones]

Hi Fluff,

Yes that works. One thing though is that it's one of those formulas that only works if you're in the same tab as the cells used to calculate, i.e. I can't say 'Method'!B2:B52, it only works as B2:B52. Thus, I calculate in the same tab and copy the answer to the tab I need it in.

Thanks a bunch,

Michele

 

[Fluff]

That formula will work quite happily on a different sheet, as long as you add the sheet name to all the ranges.

 

[kweaver]

=AVERAGE(FILTER(Sheet2!B2:B52,(Sheet2!B2:B52<>"N/A")*((Sheet2!C2:C52="ngf")+(Sheet2!C2:C52="agf"))))

As Fluff points out...works from another sheet. Maybe you typed something incorrectly when referencing the sheet with the data?!