Using LOOPS with IF to analyze specific cell ranges

**seaottr** · 02-10-2010, 05:16 PM

This might sound pretty basic, but I'm not sure how to go about doing it...I'm an Excel VBA newbie.

I have validation drop downs between specific cell ranges (A3:A9). Based on what is selected, I want to have a number associated with the selected validation option in those cell ranges.

Example:

--- Validation Options ---
Not started
Completed
Not Completed - At Fault
Not Completed - Not At Fault

Let's just say the following is selected:

A3 = Completed
A4 = Completed
A5 = Not Completed - At Fault
A6 = Completed
A7 = Completed
A8 = Not Completed - Not At Fault
A9 = [Blank]

I want to count the total of either "Completed" or "Not Completed - At Fault" (ignoring blanks and the other selections) within A3-A9 and display in another worksheet called "Team 1" within the same workbook in cell A1 the average of "Not Completed - At Fault" to the total count described above. In other words 4/5 = 80%

Can you guys please help!!!????

Thanks!

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

In other words 4/5 = 80%

Your post isn't worded clearly, but this formula returns the result ( I think ) you indicated. Adjust references as needed.

=COUNTIF(Sheet1!A3:A9,"Completed")/(COUNTIF(Sheet1!A3:A9,"Completed")+COUNTIF(Sheet1!A3:A9,"Not Completed - Not at Fault"))

**seaottr** · 02-10-2010, 07:39 PM

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

Cheers!

Originally Posted by Palmetto

Your post isn't worded clearly, but this formula returns the result ( I think ) you indicated. Adjust references as needed.

=COUNTIF(Sheet1!A3:A9,"Completed")/(COUNTIF(Sheet1!A3:A9,"Completed")+COUNTIF(Sheet1!A3:A9,"Not Completed - Not at Fault"))

**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)