Summing VLOOKUP results

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.

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

Find more posts tagged with

Sheets

Healthcare

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting Unparseable error using IF function
Hello, I am trying to use the IF formula to perform a date calculation based on a value selected in another column but keep getting UNPARSEABLE error. Here is the formula that I have used. I have also pasted the screenshot for reference. Can anyone help me out on this? =IF([SLA]@row = "Standard",[Feasibility_Start…
SUMIF cell values land within a given week
Hey, I've gotten some fair formula experience over the last year (mostly through googling and this searching this forum) but hit a brick wall with my latest ambition… I have a smartsheet in Gantt mode tracking events and sizes relating to the event. I'd like to be able to use a date picker in the summary tab to pick a…
IF/OR Formula issue
Hi, I'm trying to pull data from another sheet if the date meets the criteria in one of two columns of 12/20/2025. Should I use an if/or or nested ifs? Here is what I have so far but get the #INCORRECT ARGUMENT SET error. Here's what I have so far. Any idea what I'm doing wrong? =IF(OR(INDEX({cutdate}, MATCH([Job ID]@row ,…