AverageIF help, specific cells in multiple columns

[pwrisley]

Hi all,

New to the forum and couldn't find anything on this one. I have the following basic average formula that I need to add some sort of IF to.

=AVERAGE(I3,I8,I13,I21,I27)

Goal is to take the above and only include cells that = 100% in cells E8, E13, E21, E27. Basically, I only want the averages in the I column to compute if the corresponding E column is 100%.

Hope that makes sense, thanks for any help you can provide. Much appreciated.

 

[JPARKHURST]

You are explaining fine.

I'm tired, and made a mistake in my first formula that i couldn't find, so I broke it down into simple simple. These should really be IFS, but....results.

=SUM(SUMIF(E13,">=.1",I13),SUMIF(E3:E8,">=1",I3:I8),SUMIF(E21:E27,">=1",I21:I27))/SUM(COUNTIF(E3:E8,">=1"),COUNTIF(E13,">=.1"),COUNTIF(E21:E27,">=1"))


'Add up all of our numbers if they meet our criteria (again, this can be one Sum(SumIfs(, but I got tired of mistake hunting.  
SUM(
  SUMIF(E13,">=.1",I13),
  SUMIF(E3:E8,">=1",I3:I8),
  SUMIF(E21:E27,">=1",I21:I27)\
)


'Divided by how many of the arguments are meeting our conditions:

/

Inside out - Count, then sum up all the numbers you counted.  Again, this really should be CountIFs, but I ran into an issue.=
SUM(
  COUNTIF(E3:E8,">=1"),
  COUNTIF(E13,">=.1"),
  COUNTIF(E21:E27,">=1")
)

If it's not working, try hitting ctrl+shift+enter.
HTH,

Jon

 

[pwrisley]

You're amazing! Thank you very much. It works perfect.

 

[JPARKHURST]

Glad to hear it, and thanks!

The simplified version may be easier to work with if you are going to be adding just random cells later.

Also, don't forget when doing the complicated stuff to break it out on your sticky notes or notepad. Helps to divide and conquer.

Good luck!

Jon