Sumif and Averages?

[byepeeps]

HI,

I currently have been using the following formula for getting aggregate total of specific columns:

=SUMIF(Sheet2!$B$3:$B$435,$A2,Sheet2!$I$3:$I$435)

Now I want to use it in almost the same way but instead of an aggregate total, I'd like an average. So I would like this part of the formula !$I$3:$I$435 to produce an average. How do I do that?

Best,
Alex

[TMS]

Try:

=SUMIF(Sheet2!$B$3:$B$435,$A2,Sheet2!$I$3:$I$435)/COUNTIF(Sheet2!$B$3:$B$435,$A2)


Regards

[byepeeps]

Thanks for your reply and suggestion. It works, there's just one problem. The average isn't being calculated by the number of values, it's currently using the number of cells in the range (of which I have more than the number of sum_range cells).

It might be better to show you:

Range
heading 1
heading 1
heading 1
heading 1
heading 1

Sum_range
3
5
2

Above is an example of just one range selection and one sum selection. Currently the formula is working out an average by adding up the sum_range and diving by the number of range cells.

So in the above example the calculation would be 10/5. What I'd like to achieve is to get the average based on how many cell are in the sum_range. So in this example it would be 10/3.

Is that possible?

[TMS]

I'm sorry, I don't really understand your example.

A sample workbook with some typical data would me, and others, to picture the problem and put it into context. You're more likely to get a realistic response that way.

Regards

[daddylonglegs]

Try an "array formula" like this

=AVERAGE(IF(Sheet2!$B$3:$B$435=$A2,IF(ISNUMBER(Sheet2!$I$3:$I$435),Sheet2!$I$3:$I$435)))

confirmed with CTRL+SHIFT+ENTER

[byepeeps]

Thanks for trying to answer this. I tried 'daddylonglegs' formula but couldn't get it to work.

The formula by TMShucks is closest:
=SUMIF(Sheet2!$B$3:$B$435,$A2,Sheet2!$I$3:$I$435)/COUNTIF(Sheet2!$B$3:$B$435,$A2)

But there's just one problem. The average isn't being calculated by the number of values, it's currently using the number of cells in the range (of which I have more than the number of sum_range cells).

Please see attached dummy spreadsheet. In sheet1 is the data I want to work from. Sheet2 has the output using the above formula. I've also included a column (C) which has the correct averages in. You'll notice the averages using the above formula are slightly out because it's dividing by the amount of 'Group' cells rather than the amount of 'Group' cells with values in.

How do I fix this without removing the gaps (the main spreadsheet that I'm using this with needs to keep those gaps in the query column)?

Thanks,
Alex

[daddylonglegs]

The array version I suggested works but you need to confirm with CTRL+SHIFT+ENTER so that curly braces appear around the formula

......but as you are using Excel 2007 (or 2010?) you can use AVERAGEIF function which will ignore the blanks anyway and doesn't need "array entering", i.e. for your example

=AVERAGEIF(Sheet1!A$2:A$100,$A2,Sheet1!C$2:C$100)

[byepeeps]

Can anyone help with this? I'm still stuck. Please read the above post and see the dummy spreadsheet I attached to it.

Thanks,
Alex

[martindwilson]

or another way
=SUMIF(Sheet1!$A$2:$A$23,$A2,Sheet1!$C$2:$C$23)/SUMPRODUCT((Sheet1!$A$2:$A$23=$A2)*(Sheet1!$C$2:$C$23<>""))
but the array formula
=AVERAGE(IF(Sheet1!A2:A25=$A2,IF(ISNUMBER(Sheet1!C2:C25),Sheet1!C2:C25)))
should give the same result

[daddylonglegs]

Did you try AVERAGEIF function using the formula I suggested? I put this formula in D2 and copied it to D3

=AVERAGEIF(Sheet1!A$2:A$100,$A2,Sheet1!C$2:C$100)

see attached