SUMIFS referencing another sheet

Dana G

Hello, I'm trying to use SUMIFS formulas to create a summary grid based on another grid.

=SUMIFS({Counter}, {Submission Date}, @cell = "", {Submission Status}, @cell <> "Waived", {Submission Status}, @cell <> "N/A", {Due in the next}, @cell = "1 Week", {TAT}, @cell = 3)

The formula should sum the numbers in the Counter column if the report has a blank submission date, submission status is not one of Waived or N/A, due in the next equals 1 Week, and turn around time equals 3.

I've tried both splitting Submission Status into separate arguments and combining with AND, as well as putting quotes around the "3" but neither makes a difference. All attempts result in "0".

What am I missing? Thanks for looking!

DKazatsky

In my working version, I have a blank and it just wasn't counted. The rest continues to work as expected.

Paul Newcome

If the result is zero then there could be two issues.

1) The data in the Counter column is being stored as text.

2) There are no rows matching the range/criteria sets for the SUMIFS to grab data from the Counter column.

What happens if we count how many rows match?

=COUNTIFS({Submission Date}, @cell = "", {Submission Status}, @cell <> "Waived", {Submission Status}, @cell <> "N/A", {Due in the next}, @cell = "1 Week", {TAT}, @cell = 3)

Dana G

Thanks Paul. I confirmed there should be at least 6 rows that match. The Counter column is formatted for Text/Number.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/359872#Comment_359872

Are the numbers in the Counter column on the left or right side of the cell? Left indicates they are likely numbers stored as text, vs right which are numbers stored as values.

You can test this for sure by putting =SUM(Counter:Counter) at the bottom of the source sheet and seeing if you get a sum or an error.

If they are stored as text, you can create a helper column (which you can later hide if desired) where you convert the text-stored numbers in the Counter column to number values using the value function:

=VALUE(Counter@row)

Then use this helper column as the range to sum on your summary grid sheet.

Dana G

Thanks Jeff! The numbers were formatted center column, but I tried the formula with them on the right side and it made no difference. I also tried the sum function, and it returned a value.

DKazatsky

@Dana G

Hi Dana, have you got this working yet?

I setup your scenario and received the same results. While I was playing around, I changed the name of {Submission Status} reference to {Status} and about 30 seconds later, everything is working as expected. Not sure why (I wonder if something changed in Smartsheet itself at that time).

Also, you do not @cell in this scenario.

Hope this help,

Dave

Paul Newcome

How exactly is the Counter column being populated and with what data?

Dana G

Hi Paul, the counter column only includes numbers. Either "1" or "2" depending on the number of organizations we need to submit for, or blank if the instance shouldn't be counted. Should I use "0" instead of blanks for reports that shouldn't be counted?

DKazatsky

In my working version, I have a blank and it just wasn't counted. The rest continues to work as expected.

Dana G

Hi Dave, thanks for responding. I removed "@cell" references, but still receiving a "0" value. I'm not sure how changing the name of the column will help?

DKazatsky

Hi Dana,

I didn't rename the column, I renamed the cross sheet reference. After that, it started working, I cannot begin to explain why, or even if, that mattered.

Dana G

Interesting! I didn't realize I could change the cross sheet reference :) I'll play around with that and see if it makes a difference. Thank you

Paul Newcome

Are you manually entering the numbers or are you having them populated via formula? If via formula, can you copy/paste the formula to here?

Dana G

Good morning Paul! The numbers are manually entered.

Paul Newcome

So this yields 6:

=COUNTIFS({Submission Date}, @cell = "", {Submission Status}, @cell <> "Waived", {Submission Status}, @cell <> "N/A", {Due in the next}, @cell = "1 Week", {TAT}, @cell = 3)

But then this 0 even though the numbers in the Counter column are manually entered?

=SUMIFS({Counter}, {Submission Date}, @cell = "", {Submission Status}, @cell <> "Waived", {Submission Status}, @cell <> "N/A", {Due in the next}, @cell = "1 Week", {TAT}, @cell = 3)

Dana G

When I filter the main grid for criteria, the Counter column sum = 6. However this formula on the summary grid result = 0. I don't think I can use COUNTIFS since it won't distinguish between a 1 and 2 value, correct?