[Solved] SUM IF multiple selections

Discuss the spreadsheet application

[Solved] SUM IF multiple selections

Postby pherriot » Wed May 08, 2019 2:36 pm

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"
(33 KiB) Downloaded 23 times
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

Postby FJCC » Wed May 08, 2019 5:08 pm

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUM IF multiple selections

Postby RusselB » Thu May 09, 2019 1:11 am

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.
User avatar
RusselB
Moderator
 
Posts: 5300
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [SOLVED] SUM IF multiple selections

Postby pherriot » Thu May 09, 2019 8:40 am

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
OpenOffice 3.1 on Windows Vista
pherriot
 
Posts: 47
Joined: Wed Mar 27, 2019 1:06 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests