Using an IF statement from mulitple data sources to single source

[jmaggols]

I have a workbook with several sheets. The first sheet has one individual id numbers vertically and multiple submissions going across horizontally.
The second sheet has each individual id number listed by how many submissions...so each person's id number may be listed more than once in a column depending on the number of submissions as opposed to the first page where the id is only listed one per row with multiple submissions in the row.

Each of the different submissions has been assigned a score to be totaled at the end by the single id number. Each person has also been awarded a one-time additional score based on the amount of time with the company. Submission scores range from 0, 1, and 2. If a person receives a score of 0 on one of the submission then the score awarded for their time with the company is not to be considered, only their submission scores. Scores above 0 will be considered along with the score for the submission.

The formulas I have tried is:

=IF('4. Scoring'!L4=0,O2=0,O2+P2)

=IF(AND(A:A='4. Scoring'!A:A,'4. Scoring'!L:L=0),O2=0,O2+P2)

But both are still considering the tenure score for persons that have 0's. I need to make sure that even if a person has more than one submission and a 0 for only 1 of those submissions the tenure score and the other submissions scores are still added together.

[jmaggols]

I have also tried:

=IF('4. Scoring'!L1:L72=0,(INDEX(P2-O2,MATCH($A$2:$A$32,'4. Scoring'!A4:A1048576,0),IF('4. Scoring'!L1:L72>0,(INDEX(P2+O2,MATCH($A$2:$A$32,'4. Scoring'!A4:A1048576,0)))))))

and I am getting errors.

[Glenn Kennedy]

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[jmaggols]

It is the first and second spreadsheets that I am working with.

[Glenn Kennedy]

Your desription doesn't tally witht he column headings. what I THINK you mean is:

IF any of the scores for one person in column L of the scoring sheet =0, then column Q = Column P. However, if none are = 0, then column Q=column O +column P.

please confirm or correct....

[Glenn Kennedy]

If I'm correct in my interpretation; try this in Sheet 2, Q2, copied down.

=IF(A2="","",IF(MIN(IF('4. Scoring'!$A$4:$A$72='2. Submitted Exemplars'!A2,'4. Scoring'!$L$4:$L$72))=0,P2,O2+P2))

It is an array formula.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[jmaggols]

This almost works...the issue is that I only need the score in column P excluded if they received 0's on all submissions. If they submitted 3 pieces of work and received a 0 on one and a 1 on the others, I do need the score in Column P to be added with the score in column O.

[Glenn Kennedy]

... in which case; try this, instead:

=IF(A2="","",IF(SUMIF('4. Scoring'!A:A,'2. Submitted Exemplars'!A2,'4. Scoring'!L:L)=0,P2,O2+P2))

[jmaggols]

Absolutely perfect!!!

Works beautifully...thanks!

[Glenn Kennedy]

Thanks for the Reputation that you have added. It's much appreciated. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )