[Solved] SUM IF multiple selections

[pherriot]

Hi People.
A simple quality evaluation form with 13 questions.
Upon scoring Agents, there will be either 10, 11, 12 or 13 questions to be marked on. This means the calculation I use to provide averages as a percentage is off.

Problem cell....
CELL D43 = FORMULA = "SUM(D43/130)"
So in summary I need a way to change the calculation from =SUM(D43/130) to
=SUM(D43/120)
=SUM(D43/110)
=SUM(D43/100) depending on whether cells D7, D25 and D28 have a selection of "N/A".

I feel I need something like below but am unsure....
IF only CELL D7 = "N/A" then =SUM(D43/120)
IF only CELL D25 = "N/A" then =SUM(D43/120)
IF only CELL D28 = "N/A" then =SUM(D43/120)
IF 2 cells = "N/A" then =SUM(D43/110)
IF 3 cells = "N/A" then =SUM(D43/100)
Forgive my ignorance, Im learning.
Cheers.
Paul.

[FJCC]

It seems you want this formula in D47

Code: Select all

=D43/((13- COUNTIF(D4:D41;"N/A"))*10)

The COUNTIF() will return the number of cells that contain N/A in the range D4:D41.

[RusselB]

I was going to suggest the SUMIFS function, then I realized that the OP is using division, not summation... still might work, but FJCC's suggestion appears to be on the money.

[pherriot]

That works amazing FJCC.
I can see I have some learning to do with regard to ranges and formula efficiency. My attempts are long drawn out strings which are then difficult to read.

Thanks FJCC.
And RusselB.
Cheers lads.
P