CountIF Formula

[kcroft88]

Hi, I am looking for some help with a formula. I am creating a training matrix. the training values are 1,2,3,4 (1=training, 2=trained with minimal supervision, 4 = fully trained etc)
My current formula is.. =COUNTIF(AR7:BB7,">1")*4
AR7:BB7 is my range I'm looking at, ">1" is saying count anything over 1, the * 4 is relative to the value of point available (4=fully trained).
Another cell references this to then return a % of how much training they have in the range on AR7:BB7. the training values 3 and 4 are people fully trained who require no support so I definitely want to count those as whole numbers. I'm counting level 2 as they have some training.
the formula I need help with is as level 2 requires some supervision I don't want to count that as a whole number. how can I edit my formula to say if in the range of AR7:BB7 anyone is a level 2 then divide that value in half so its not counted as a whole number? but the 3 and 4 value is still counted as a whole?

If it works here is a screenshot... http://tinypic.com/r/jhtyz6/9

Cell BD has the formula I'm looking for and cell BC is returning my %.

 

[FormR]

Hi. I think I understand and here is one option you can try:

=SUMPRODUCT(COUNTIF(AR7:BB7,{2,">2"}),{0.5,1})*4

 

[kcroft88]

Thank you very much for your response and it has worked a treat. EXACTLY what I was looking for. For my learning I would love to know the meaning of each item of the formula but probably not easy to explain on a forum so ill do a little googling around it,

Thank you again for taking the time to help

 

[FormR]

For my learning I would love to know the meaning of each item of the formula

Hi, I'll give it a go.

Because we are passing an array to the criteria part of the COUNTIF() formula it returns an array containing the results of each criteria.

So we get an array containing {Count of the 2's, Count of the >2's}

SUMPRODUCT() sums the product of the corresponding ranges of the arrays passed to it. The first array being the two results from the COUNTIF() the second being the static array {0.5,1}

So we end up with SUM(Count of the 2's * 0.5 , Count of the >2's *1)

 

[kcroft88]

That was actually easier to explain than I thought and I understand it completely.

I certainly do enjoy learning new formulas and their meanings so thanks once again