SUMIFS & Mulitiplication - Formula asistance

June 19, 2016 at 14:18:14
Specs: Windows 64
Hi, I hope someone is online to help me today (Sunday)

I've been searching Google for an answer for the last 3 hours (maybe 4). I am in need of a formula that I can add all of the following conditions/sum/multiplication:

Please note: all data has been inputted on sheet 1 unless otherwise indicated.


Linda Scott's name is in cell A23
Total # of cases worked by Linda B23 (all errors have a point value)
Admin errors only (on sheet 2- name of sheet is Raw Data) cell V:V
(SUM) total number of MAXIMUM achievable points ****Total # of cases worked (B23) * 100***
Subtract (Total # of cases work (B23) * 100) from the maximum total of acheived points

?????

Thank you in advance!!!


See More: SUMIFS & Mulitiplication - Formula asistance

Report •


#1
June 19, 2016 at 15:54:30
I am having a difficult time deciphering what it is you want.

(SUM) total number of MAXIMUM achievable points ****Total # of cases worked (B23) * 100***
Subtract (Total # of cases work (B23) * 100) from the maximum total of acheived points

I'm guessing that Maximum Achievable points is the contents of cell B23 times 100.
Correct?

How do I know/determine what the Maximum Total of Achieved points are?
Does that have to do with column V on the Raw Data tab?

Please understand I can not see what your sheet looks like from where I'm sitting.
So you need to be very specific in explaining your needs.
It is usually easier if you use Cell Numbers as references.

I have my own ideas what Maximum Achievable Points means
but it may not be what you mean.

MIKE

http://www.skeptic.com/


Report •

#2
June 19, 2016 at 16:05:04
Can I upload the workbook some way, some how?

Report •

#3
June 19, 2016 at 16:33:03
There is no way to upload a worksheet on this board.

You can either post a small sample of your workbook using the < PRE > tags
which helps align the data, see this HOW-TO:

http://www.computing.net/howtos/sho...

So your data looks like:

       A         B      C      D      E
1) My Product  1st %  2nd %  3rd %  4th %
2)  Alpha				
3)  Beta 				
4)  Gamma				
5)  Delta				

Or you can use a site like Zippyshare, and then post the link here.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 19, 2016 at 18:27:06
Sheet #1
A B C D E F
Analyst Name # of cases reviewed # of Admin errors Quality score
Linda Scott Countif formula Need help here

Sheet #2
A B C D
Analyst Name # of cases reviewed # of Admin errors # of Procedural errors
Linda Scott 5 7 0
Linda Scott 1 0 5


E F
Deduction Maximum achievable score
1.5 *5, .75*7 100*2 (Linda reviewed 2 cases) = 200
.75*5 (add together)


I hope this makes sense because it was really hard to try to format this. Ok what I need is a formula to

countif A2=Linda Scott, then sum the # of admin errors (found in b2), then with that total (ie 7), multiply by 100(ie 700) 700 is the maxium points the analyst could possibly score. (continuing with this same formula...)then on sheet 2 column g:g the actual deducted points for EACH file for Linda Scott (and Peers) are found here. The formula would need to search sheet 2 and sum for Linda Scott only. Lastly, subtract the deducted points from the maximum achievable points (ie 700 from above)



message edited by HarrisLyfe2016


Report •

#5
June 19, 2016 at 19:41:57
This is going to take some time, so please be patient.
First, let's get our sheet layouts correct so we are both working with the same data.

Sheet 1 looks like: ( I have abbreviated your headings)

     A                B                C             D
1) Analyst       # cases rvwd   # Admin errs   Quality score
2) Linda Scott   

Sheet 2 looks like:

     A                B                C             D            E        F
1) Analyst       # cases rvwd   # Admin errs   # Prcdurl errs   Dductn   Max score
2) Linda Scott       5                7             0               
3) Linda Scott       1                0             5               

Are my sheets correct?

You included this data:

1.5 *5, .75*7 100*2 (Linda reviewed 2 cases) = 200
.75*5 (add together)

but I don't know where it goes or what it is supposed to represent.
Are they formulas of some type?
Where does the 1.5 and .75 come from?

then on sheet 2 column g:g

You do not have a column G listed.....


When using the < PRE > tags, I have found it easier to convert the TAB's in the data, to Spaces.
Excel uses TABs as it's delimiters.
To accomplish this use a Text Editor or Notepad, then do a Search and Replace.
For Notepad it is search for: ^T
and replace with an actual space.

MIKE

http://www.skeptic.com/


Report •

#6
June 19, 2016 at 20:12:25

Sheet 1 (This is the sheet I need the formula for F2)


A B C D E
1) Analyst # cases rvwd # Admin errs Quality score Maximum points
2) Linda Scott Countif ... SUMIF =E2 - .75*C2 100*7


F
NEED Linda Scott (only) A2, admin error max pts E2 - D2


Sheet 2 (all of Linda Scott's errors are inputted seperately on this tab from A2:A1500 along with other analyst)

A B C D E
1) Analyst # cases rvwd # Admin errs # Prcdurl errs Dductn
2) Linda Scott 5 7 0 Formuala .75*7
3) Linda Scott 1 0 5 Formuala .75*0

message edited by HarrisLyfe2016


Report •

#7
June 19, 2016 at 20:33:34
Please read the HOW-TO on the use of < PRE > tags and repost,
I have no idea what goes where.

MIKE

http://www.skeptic.com/


Report •

#8
June 20, 2016 at 08:50:38
Do you have a link to the how to? I've searched on this site and watched a few Youtube Videos and still, I cannot get the text to align as you have.

And to make matters worse, my job has blocked the use of Zippyshare

message edited by HarrisLyfe2016


Report •

#9
June 20, 2016 at 09:41:29
The link was provided in Response #3. Here it is again:

http://www.computing.net/howtos/sho...

You can also access the How-To by clicking the last line in this post.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
June 20, 2016 at 09:45:47
My apologies, I did not see it.

Report •

#11
June 20, 2016 at 10:00:37
I've spent a ton of time on trying to get this text to align correctly and I am failing horribly. I have a meeting at 2pm today with my Mgr and since Thursday I've been trying to figure out these few formula's - no fault of anyone but my own. Thank you for your time, I dont know what I plan to do at this point.

Report •

#12
June 20, 2016 at 11:49:53
I am back! Meeting was worse then I thought but its over now so lets see if I can post the formula and you guys/gals can take it from there. Please ignore all previous post - I dont want to further confuse you (or myself).

=COUNTIFS('Raw Data'!L:L, A23,'Raw Data'!P:P,"=>30",'Raw Data'!G:G,"<>CRE",'Raw Data'!G:G,"<>GIB")

I am trying to get the total # of Admin errors (not the sum). Let me break down this formula

Sheet (Raw Data) is where the actual data is inputted.

On sheet 2 (where the formula needs to be added) - The formula above is suppose to count the # of Admin errors for Linda Scott. In this formula L:L is where Linda's name will appear for each error. A23 (on sheet 2) is Linda Scott name (this is the reporting sheet) I am asking Excel to look at A23 (Linda Scott) name on Sheet 1 and count the # of Admin errors logged. Unfortunately the only way for Excel to do this - is if there is a #=>30 in cell P:P (sheet 1) then that should be counted for Linda. I also need to exclude CRE and GIB which are listed in column G:G on sheet 1.


I hope this will help you help me!

message edited by HarrisLyfe2016


Report •

#13
June 20, 2016 at 12:32:13
Swap your => operators.

">=30"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
June 20, 2016 at 12:40:52
re: "The formula above is suppose to count the # of Admin errors for Linda Scott. "

No, it's not.

In reality it is counting the number of occurrences where all of these criteria are true:

L:L = A23
PP >=30
GG <> CRE
GG <> GIB

Granted, this may result in the value you are looking for, but it is not really counting the admin errors.

That is a subtle but very important difference.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
June 20, 2016 at 13:09:15
Thank you! Can you take a swag at this too?

=IF(G5=0,"Pass","")*IF(G5>=97,"Pass","Fail")

I keep getting a #VALUE! error message.

G5=0 (Because this Analyst may not have reviewed files this month)
G5>=97 for Analyst who did review cases and got an actual score

Please keep in mind that anything 96.99 and below (but not =0) is a Fail

Any idea?


Report •

#16
June 20, 2016 at 13:23:05
Tried this and everything is Pass now - OMG!

=IF(OR(G23=0,G23<=97),"Pass","Fail")
=IF(AND(G23=0,G23<=97),"Pass","Fail")

message edited by HarrisLyfe2016


Report •

#17
June 20, 2016 at 13:32:52
Looks like I may have figured it out

=IF(AND(G24=0,G24<=97),"Pass","Fail")


Report •

#18
June 20, 2016 at 16:11:12
It looks as if you keep asking different questions about different formulas. In order to keep the archives organized, please start a separate thread for each separate question.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
June 21, 2016 at 05:42:22
Yes, I sure did & I did not realize it until after I reread my post. I understand & Thank you for pointing that out. I am new to forums all together, but everyday I learn something new about forum postng etiquette,

Appreciate your patience!


Report •


Ask Question