# AverageIf based on another columns criteria

1. ## AverageIf based on another columns criteria

Am a little stuck with the average if function.

I want to average the values in column G (where populated) if Column A=PHA

It's monday afternoon! well,that's my excuse for being rubbish today!

2. ## Re: AverageIf based on another columns criteria

Try like this

=AVERAGEIF(A:A,"PHA",G:G)

3. ## Re: AverageIf based on another columns criteria

Try like this

=AVERAGEIF(A:A,"PHA",G:G)
That works fantastically except where I have a few zeros as I don't want to count these either in G:G

Sorry, I should have been clearer in the first place.

4. ## Re: AverageIf based on another columns criteria

OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i.e.

=AVERAGEIFS(G:G,G:G,">0",A:A,"PHA")

5. ## Re: AverageIf based on another columns criteria

OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i.e.

=AVERAGEIFS(G:G,G:G,">0",A:A,"PHA")
Spot on, Thank you

One last one, just need to add in one further criteria to the above

the average also needs to be based on figures in column H (including zeros) so, G & H together against A.

6. ## Re: AverageIf based on another columns criteria

Do you want the average of the total of columns G and H? You won't be able to use AVERAGEIFS for something like that, you need an "array formula" like this

=AVERAGE(IF(A1:A100="PHA",IF(G1:G100+H1:H100>0,G1:G100+H1:H100)))

confirmed with CTRL+SHIFT+ENTER

7. ## Re: AverageIf based on another columns criteria

Do you want the average of the total of columns G and H? You won't be able to use AVERAGEIFS for something like that, you need an "array formula" like this

=AVERAGE(IF(A1:A100="PHA",IF(G1:G100+H1:H100>0,G1:G100+H1:H100)))

confirmed with CTRL+SHIFT+ENTER
No, I'm not explaining this very well (Can't post the sheet due to data protction act)

This is what I want but, it needs to check column E which must =PHA. If column E does not =PHA, then this must not be included.

=AVERAGEIF(Properties!BU3:BU350,">0",Properties!BV3:BV350)

I know this won't help, but the answer is -13.48 lol.

=AVERAGEIF(Properties!E3:E350,"PHA",Properties!BV3:BV350)

with this, I get -8.75

8. ## Re: AverageIf based on another columns criteria

I'm stupid, sorted. Thanks very much for your help.

Final equation;

=AVERAGEIFS(Properties!BV3:BV350,Properties!BU3:BU350,">0",Properties!E3:E350,"PHA")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1