COUNTIF Formula to count multiple values from Column in another sheet

Daniel Vitter

I've successfully set up COUNTIF formulas to reference a single column from another sheet when looking for only one value. I'm now trying to set up a formula to look at a column in another sheet and count up multiple values for a total. The column is single-select.

Status (Name of the column in the sheet I'm referencing)

In Production (Value 1)

Edits Needed (Value 2)

Approved - Needs HR PDF (Value 3)

I'd like a formula that references this column and counts up the cumulative total of the three values.

Here's what I have started so far: =COUNTIF({RAT Status Column}, "In Production" + "Edits Needed" + "Approved - Needs HR PDF")

From what I've read I need to include a reference to the range each time I look for the value, but I'm stumped. Lots of "UNPARSEABLE" AND "INCORRECT ARGUMENT" coming at me.

Curveball: The last thing I'd like to add to the eventual formula above is to also exclude any row where the "Ad Completed" column is checked.

Please let me know what other details you need from me. Hopefully this is a simple answer.

DKazatsky2

Hi @Daniel Vitter,

Give this a try.

=COUNTIFS({Status}, "In Production", {Ad Completed}, =0) * 1 + COUNTIFS({Status}, "Edits Needed", {Ad Completed}, =0) * 2 + COUNTIFS({Status}, "Approved - Needs HR PDF", {Ad Completed}, =0) * 3

Hope this helps,

Dave

Daniel Vitter

@DKazatsky2 I got your formula to work!

I simply removed the asterisk and number you had at the end of each part of the formula.

=COUNTIFS({RAT Status Column}, "In Production", {RAT Ad Completed Column}, =0) + COUNTIFS({RAT Status Column}, "Edits Needed", {RAT Ad Completed Column}, =0) + COUNTIFS({RAT Status Column}, "Approved - Needs HR PDF", {RAT Ad Completed Column}, =0)

I then used this same formula to count two other status options that needed to be added together.

Thanks again! Very helpful. Cheers!

DKazatsky2

Hi @Daniel Vitter,

Give this a try.

=COUNTIFS({Status}, "In Production", {Ad Completed}, =0) * 1 + COUNTIFS({Status}, "Edits Needed", {Ad Completed}, =0) * 2 + COUNTIFS({Status}, "Approved - Needs HR PDF", {Ad Completed}, =0) * 3

Hope this helps,

Dave

Daniel Vitter

Thanks @DKazatsky2 — Once I edited the referenced ranges (columns in another sheet) it did end up returning a number. However, the number it returned was 106, when in fact it should be 82. The only reason I know it should be 82 is we have a report built to show just the rows that meet this criteria. However, Smartsheet doesn't have a function by which we can reference a summary in a report, or a way to run a COUNTIF formula on a report. So, that's why I'm trying to rig up this formula to return the same number I already have in the report.

Sorry, I know that's a lot to unpack. I'll keep tinkering with the formula you shared. It at least got me further down the path.

I suppose I need to put in a feature request to Smartsheet to add reports to the formula possibilities.

Thanks again.

Daniel Vitter

@DKazatsky2 I got your formula to work!

I simply removed the asterisk and number you had at the end of each part of the formula.

=COUNTIFS({RAT Status Column}, "In Production", {RAT Ad Completed Column}, =0) + COUNTIFS({RAT Status Column}, "Edits Needed", {RAT Ad Completed Column}, =0) + COUNTIFS({RAT Status Column}, "Approved - Needs HR PDF", {RAT Ad Completed Column}, =0)

I then used this same formula to count two other status options that needed to be added together.

Thanks again! Very helpful. Cheers!

DKazatsky2

@Daniel Vitter

Glad it worked out.