repeat sum IF for mulitple cells

Tammy Parker

I have column "Location".. then many rows of citifies with lots of data for each city.

I need to summarize my data "By Question" AND "Location" - however it does not work to "drag" or to copy paste. I have to change all my information in every cell... I have hundreds. Is there an easy way?

In Excel, I could drag and it would change information automatically to do same formula but with the new cell data. Does this work somehow in Smartsheet.

=SUMIF([Location (for summary)]1:[Location (for summary)]194, CONTAINS("Arden Hills", @cell), [Corrected Number of Employees]1:[Corrected Number of Employees]194)

My next cell would be SAME Location 1 - Location 194 but I need Arden Hills to be changed to Atlanta. It does not even keep the 1 through 194 when I drag, it changes all my numbers.....

Not sure if this makes sense... LOL

Genevieve P.

Hi Tammy,

There are a few things we can do to make this formula a bit smoother. The first thing is to take out the Row References (the 1 and 194) and instead reference the entire column (unless you wanted it to stop at 194?) This will then update as new rows are updated without any adjustment to the formula.

Ex:

=SUMIF([Location (for summary)]:[Location (for summary)], CONTAINS("Arden Hills", @cell), [Corrected Number of Employees]:[Corrected Number of Employees])

See here for more information on referencing columns in formulas.

Now, based on how this is set up, you would need to re-type out the criteria you're looking for in the CONTAINS function, "in these". Another way to do this would be to create a chart to reference.

You'd want to have one column that lists out each of the places/locations that you're going to search for. You can then use what's typed in that cell within your formula, by using the @row function. This will look at the cell in that row as the criteria:

ex:

=SUMIF([Location (for summary)]:[Location (for summary)], CONTAINS([Helper Column]@row, @cell), [Corrected Number of Employees]:[Corrected Number of Employees])

Now this formula will be relevant across the entirety of both columns, and it will update to look at the content next to it in the Helper column, so you don't need to change it again. You just need to update the content in the chart.

Here's an example in a sheet:

Keep in mind that if the content in the Helper Column is exactly what's appearing in the Location (for summary) column, you actually don't need CONTAINS... you can just search for the criteria, like so:

=SUMIF([Location (for summary)]:[Location (for summary)], [Helper Column]@row, [Corrected Number of Employees]:[Corrected Number of Employees])

Let me know if this works for you, or if you have any questions!

Cheers,

Genevieve