Totaling Scores Conditionally

emma_rock

Hi all,

I'm putting together a resume scoring Smartsheet and want to total five domain values.

Applicants can apply for either our quantitative analyst or qualitative analyst position. Some apply for both. All applicants receive scores (0-2) (dropdown) for 4 categories (relevant coursework, interest. etc). Quantitative applicants receive a score for "Quantitative Skills", while qualitative applicants receive a score for "Qualitative Skills"; those who apply to both positions receive scores for both.

If the applicant applies for quant, they receive no score for qual (and vice versa).

I have a "Final Score - Qual" and "Final Score - Quant" column for each applicant. If the candidate is a quantitative applicant (so doesn't receive a score for qualitative skills), how do I get the "Final Score - Qual" sum to show false or N/A?

Thanks!

emma_rock

I tried this and got an error.

IF([Requisition Type]@row <> "Qualitative", =SUM([Relevant Degree/Coursework]@row, [Demonstrated Interest in Health Policy]@row, [Research/Analysis Experience]@row, [Quantitative Skills]@row, [Substantive Areas of Interest]@row)

Any thoughts?

Paul Newcome

All you need to do is take that last formula from yesterday and remove the = from before the SUM function.

Paul Newcome

Are you able to provide a screenshot for context (sample data can be used if needed)?

emma_rock

Paul Newcome

Try starting your formulas off with an IF statement.

=IF([Requisition Type]@row <> "Qualitative", rest of quantitative formula)

and

=IF([Requisition Type]@row <> "Quantitative", rest of qualitative formula)

emma_rock

I tried this and got an error.

IF([Requisition Type]@row <> "Qualitative", =SUM([Relevant Degree/Coursework]@row, [Demonstrated Interest in Health Policy]@row, [Research/Analysis Experience]@row, [Quantitative Skills]@row, [Substantive Areas of Interest]@row)

Any thoughts?

Paul Newcome

Try removing the = from before the SUM function.

emma_rock

Here is our new attempt.

=SUMIF([Requisition Type]:[Requisition Type], "Quantitative"), [Substantive Areas of Interest]@row:[Substantive Areas of Interest]@row, [Relevant Degree/Coursework]@row:[Relevant Degree/Coursework]@row, [Demonstrated Interest in Health Policy]@row:[Demonstrated Interest in Health Policy]@row, [Research/Analysis Experience]@row:[Research/Analysis Experience]@row, [Quantitative Skills]@row:[Quantitative Skills]@row, [Substantive Areas of Interest]@row:[Substantive Areas of Interest]@row)

Paul Newcome

All you need to do is take that last formula from yesterday and remove the = from before the SUM function.

emma_rock

Thank you!

Paul Newcome

Happy to help. 👍️