## [Solved] SUM IF multiple selections

### [Solved] SUM IF multiple selections

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.
Attachments
Quality Agent TEMPLATE - latest tester.ods
Sheet "Calls Sheet 1"
Last edited by pherriot on Thu May 09, 2019 8:37 am, edited 1 time in total.
OpenOffice 3.1 on Windows Vista
pherriot

Posts: 47
Joined: Wed Mar 27, 2019 1:06 pm

### Re: SUM IF multiple selections

It seems you want this formula in D47
Code: Select all   Expand viewCollapse view
`=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.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm

### Re: SUM IF multiple selections

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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5300
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: [SOLVED] SUM IF multiple selections

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.