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!!!

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 pointsI'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

Can I upload the workbook some way, some how?

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

Sheet #1

A B C D E F

Analyst Name # of cases reviewed # of Admin errors Quality score

Linda Scott Countif formula Need help hereSheet #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 tocountif 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

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 ScottSheet 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 5Are 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:gYou 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

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

Please read the HOW-TO on the use of < PRE > tags and repost,

I have no idea what goes where.MIKE

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

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.

My apologies, I did not see it.

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.

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

Swap your => operators. ">=30"

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

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 <> GIBGranted, this may

in the value you are looking for, but it is not really counting the admin errors.resultThat is a subtle but very important difference.

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

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 scorePlease keep in mind that anything 96.99 and below (but not =0) is a Fail

Any idea?

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

Looks like I may have figured it out =IF(AND(G24=0,G24<=97),"Pass","Fail")

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.

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!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History