Summing VLOOKUP results

Cody Reynolds

Hey community,

I'm creating a way to judge submissions, where each judge has their own smartsheet (so they can see/edit their own markings but not be influenced by others).

I need to be able to know (1) how many times a submission was judged, and (2) what the total score is on one single sheet. 

1. Currently I've drafted the following for two reviewers, which works, but will be extra clunky once I have the full 11 reviewers: [=IF(ISBLANK(Innovation2), 0, COUNTIF({Reviewer 1 Range 1}, Innovation2)) + COUNTIF({Reviewer 2 Range 1}, Innovation2]  Is there a more elegant way to write this?
2. What I've drafted doesn't work because it references two separate referenced ranges.  What's the best solution?  [=SUM(VLOOKUP(Innovation2, {Reviewer 1 Range 2}, 3), VLOOKUP(Innovation2, {Reviewer 2 Range 2}, 3))]

Thanks All!

 

Paul Newcome

Without an overhaul of your initial setup, you are looking at the best way to collect this information onto one sheet. 

 

The problem with your SUM can be solved by treating it the same way you di your COUNTIF.

 

=SUM(...) + SUM(...) + SUM(...)

Eric M Oliveira

Hi Cody.

Happy to help you achieve your desired goals. I've answered your questions in the order provided. 

1. To give judges the ability to judge their own markings but not be influenced by other judges, I would suggest one of the below options. 

Option 1: Could achieve utilizing a Form/Update Request specific for that Judge, that way all of the Data automatically end up on one sheet instead of multiple sheets.

Utilizing Forms: https://help.smartsheet.com/learning-track/smartsheet-intermediate/forms

Utilizing Update Requests: https://help.smartsheet.com/learning-track/smartsheet-intermediate/update-requests

 

Note: In both cases, you will want to remove the undesired fields/columns from the above-stated items.

Option 2: If you'd prefer to have each user have their own sheet you can achieve this goal utilizing a cross-sheet VLOOKUP formula. To utilize cross-sheet VLOOKUP you'll want to create a Submission number on the source sheet that the cross-sheet VLOOKUP can search for. They on the Destination sheet create the cross-sheet VLOOKUP to search for the desired value than produce the column information the Judge needs to see. This will require a little extra work because you'll need to adjust the cross-sheet VLOOKUP slightly for each column to pull the desired column. The cross-sheet VLOOKUP could look like this: =VLOOKUP("S001", {Source Sheet Range 1}, 2, false)

Note: Note you would need to update the range and the # 2 for each column on the new sheet and you'd have to, in theory, do this for each judge's sheet. This Help Center article outlines how to utilize a VLOOKUP: https://help.smartsheet.com/function/vlookup

2. To know how many times each submission was judged you can perform a count of the submission based on if it was judged, then add up the counts. This could be done utilizing a COUNTIFS. For Example, let's say each judge had their own column or sheet for the one submission you would utilize the submissions name as the criteria and perform the count in the Judges column. The formula could look like this:

=COUNTIFS([Submission Column]:[Submission Column], "Submission Name", [Judge 1]:[Judge 1], > 0)

COUNTIFS allows you to add additional criteria to be considered for the same count. In this case the Submission name and if the judge has scored the submission. https://help.smartsheet.com/function/countifs

You can then SUM these counts in one of two formula.

Option 1. =SUM([Judge 1 Total]:[Judge 4 Total]) 

Option 2. =SUM([Judge 1 Total], [Judge 2 Total], [Judge 3 Total], [Judge 4 Total])

3. To obtain the total score in one single sheet you could utilize a SUMIF or SUMIFS depending on the amount criteria you're looking to SUM. These Help Center article outlines how to utilize these functions:

SUMIF: https://help.smartsheet.com/function/sumif

SUMIFS: https://help.smartsheet.com/function/sumifs

If further assistance is needed I'd suggest connecting with our support team. They then can jump on a quick screen share to achieve your desired goal. 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support