SUMIFS not working (referencing another sheet)

EBooher

I'm struggling with getting a SUMIFS function to work properly that is referencing another sheet.

So on my sheet with the function, I have it as:

=SUMIFS({Total GL Detail Range 4}, {Total GL Detail Range 5}, Project8, {Total GL Detail Range 3}, Title@row)

...where the "Total GL Detail" is the other sheet referenced. Range 4 is the column of dollar amounts I'm wanting to summarize, Range 5 is a column of project ID numbers (and the Project8 cell referenced is the location in the current sheet where the specific project ID I want is referenced), and Range 3 is a column of three words ("Revenue", "Cost", and "Margin"...Title@row is the location in the current sheet where the word "Revenue" is referenced). So I'm basically trying to summarize data based on a specific Project ID and for a specific type of dollar (Revenue).

The function just gives me a $0 when the total should be an actual sum of dollars. Any idea where I'm going wrong? (Or if there's a smarter way to do this?) Thank you!

Paul Newcome

Ok. I just had someone else earlier this week have the same issue. Even after converting all of the IDs to text it still wasn't working. Lets try converting everything on the source sheet to numbers. Take that LEFT formula you have that strips the ID out and wrap it in a VALUE function.

=VALUE(LEFT(.......................))

Then try the COUNTIFS on the ID again, but instead of referencing the helper cell in the formula sheet that we created, reference the linked cell that houses the numerical value.

Paul Newcome

There are a number of reasons for this to output a zero, so lets start with the most basic and work our way down the list from there.

How exactly are the dollar amounts in the source sheet being populated?

EBooher

The dollars are being populated by a function. The specific function is: =-Net@row which references another column in that sheet which is actually another function itself.

Paul Newcome

What is that source function?

EBooher

The function in the Net column is: =Debit@row - Credit@row which references two other columns in that source sheet. The Debit and Credit columns contain data (no functions) that was pasted in from Excel. If there is ever a Debit amount, there is no corresponding credit amount directly beside it in the same row (and vice versa with the Credit column). The goal of the Net column is to compile both columns together with all debit amounts showing as positive and all credit amounts showing as negative. The purpose of the Dollars column (which is the Range 4 in the initial function in the other sheet) is to flip the signs. (For other purposes, I wanted two columns showing the signs going both ways.) Both the Net column and the Dollars column are setup as function columns, not as cell functions.

(I apologize for my naivety...I'm brand new to Smartsheet.)

Paul Newcome

So it sounds like we are definitely pulling in numbers. We can move on to the next piece. Your ID columns...

How are they populated, and are you able to provide some samples of that data?

EBooher

So the Project ID numbers are the Primary column and are also a column function. They are simply six digit numbers like "409642". The function in the column is: =LEFT([TS Project - Full Name]@row, 6) which references another column that has the project's full name and number (e.g. "409642 - Project X" and it's always in that format). That "TS Project - Full Name" column is also another column function and is: =MID([Line Description]@row, (FIND("#", [Line Description]@row) + 1), 100) which strips data from a "Line Description" column that has pasted data in from Excel. The project numbers and names always follow the # sign in the Line Description.

Paul Newcome

I think we may be on to something. How are you entering the project numbers in the second sheet (the one with the SUMIFS)?

EBooher

So the SUMIFS function pulls the Project ID number from another cell in the same sheet (so not the referenced sheet). That cell is in a different column but is merely down below my data. Right now, the cell is linked to another third sheet (called "Project Metadata"), but I've tried it where the Project ID number was merely hard-keyed in (still $0 result) as well as the Project ID number being linked in another separate cell in the sheet but then merely referenced in the cell that is used in the SUMIFS function (still $0 result). I've always either gotten a $0 or an error as an answer (can't remember exactly what error I've gotten in the past).

I attached a screenshot of the sheet where the SUMIFS formula is found...the cell where the SUMIFS formula lies is in the cell boxed in red and the Project ID number is pointed out with a blue arrow.

Paul Newcome

Ok. I think we may have found the problem. In your screenshot, the linked in cell containing the project number is right justified. Assuming you have not applied any formatting to that cell, this shows that it is being stored as a numerical value. MID and LEFT functions output text strings. While they may LOOK the same, one is considered a number and the other is considered text which means you aren't going to get a match.

In your most recent screenshot, enter this into the cell immediately to the right of the project number that is being linked in:

=Budget@row + ""

"Adding" double quotes to the end of the cell reference like that will convert it from a number to a text string. You should see that this new one is now right justified within the cell.

What happens if you reference this instead of the linked in ID?

=SUMIFS({Total GL Detail Range 4}, {Total GL Detail Range 5}, [Revised Budget]8, {Total GL Detail Range 3}, Title@row)

EBooher

That makes sense...I've made that change now but it's still showing as $0 so I'm wondering if there's something else at play here. But at least that issue has been fixed.

I've attached an updated screenshot as well as a screenshot of the columns (ranges) used in the function from the sourced sheet in case there's anything visual that would help there

.

Paul Newcome

The only thing that leaves us with is the Title piece, but that looks like it should be ok based on your screenshots. Lets switch over to a COUNTIFS for a little bit to do some additional troubleshoot.

We can start with two different ones. One for each of the range/criteria sets (ID and Title done separately).

=COUNTIFS({Total GL Detail Range 5}, [Revised Budget]8)

=COUNTIFS({Total GL Detail Range 3}, Title@row)

EBooher

Okay so the first COUNTIFS function on the Project ID numbers (the [Revised Budget]8 cell) came up with 0 which means it's not working correctly.

The other COUNTIFS function (the Title@row cell) worked appropriately and counted a total of 19 instances of the word "Revenue".

So it's something to do with the Project ID numbers from the source sheet...

Paul Newcome

Ok. I just had someone else earlier this week have the same issue. Even after converting all of the IDs to text it still wasn't working. Lets try converting everything on the source sheet to numbers. Take that LEFT formula you have that strips the ID out and wrap it in a VALUE function.

=VALUE(LEFT(.......................))

Then try the COUNTIFS on the ID again, but instead of referencing the helper cell in the formula sheet that we created, reference the linked cell that houses the numerical value.

EBooher

AHA! That worked!! And oddly enough, I didn't even need to change the helper cell reference that was in text form. It worked with both the text cell and the numerical cell. Just had to be sure to change the range it was referencing to numeric.

Thank you so much!!

Paul Newcome

Happy to help. 👍️

@Genevieve P. Have there been any changes that you know of that would impact this functionality?

In the past, if we had an ID column that had leading zeros on some but not in others, we could use the [Column Name]@row + "" method to convert everything into text, and matches would work just fine. This one is now twice this week that it didn't work and we had to use the VALUE function to convert the IDs into numbers to get it to work.