Using LOOPS with IF to analyze specific cell ranges

**beaunydal** · 02-10-2010, 07:45 PM

Originally Posted by seaottr

I want to count the total of either "Completed" or "Not Completed - At Fault" (ignoring blanks and the other selections) within A3-A9

=SUMPRODUCT(--(Sheet1!A3:A9="Completed")+--(Sheet1!A3:A9="Not Completed - At Fault"))

Originally Posted by seaottr

average of "Not Completed - At Fault"

I don't know what you mean by this, you can only average numbers.
If you mean count of "Not Completed - At Fault" then...
=COUNTIF(Sheet1!A3:A9,"Not Completed - At Fault")

I'm also not clear on how you got the 4/5 or 80%.

Beau Nydal

**Palmetto** · 02-10-2010, 07:52 PM

Thanks so much! That's exactly what I needed!

Glad to be of help.
If you are satisfied with the solution, please mark your thread as solved by going to the top of YOUR first post and click on Thread Tools, then Go Advanced and choose the [solved] prefix.

You may also leave feedback to the who helped with your solution. See my signature.

**DonkeyOte** · 02-11-2010, 03:10 AM

Originally Posted by beaunydal

=SUMPRODUCT(--(Sheet1!A3:A9="Completed")+--(Sheet1!A3:A9="Not Completed - At Fault"))

Beau Nydal, just an FYI but the double unary coercion in the above is not required given the addition taking place in the OR.

The below will do the same but with fewer operations:

=SUMPRODUCT((Sheet1!A3:A9="Completed")+(Sheet1!A3:A9="Not Completed - At Fault"))

That said I think the COUNTIF approach is proabable the more logical given it's lightweight nature... if the terms being searched for are not variable you could use an inline array construct if preferred

=SUM(COUNTIF(Sheet1!A3:A9,{"Completed","Not Completed - At Fault"}))

Very much horses for courses - the above syntax is shorter (esp. when there are a number of terms) however it is not very flexible should you want to quickly adapt the terms themselves (ie via cell links etc)