Sum values in Column B for distinct values in column A

Cassie Walker

Hi All,

So we use smartsheet to have a running yearly log of samples submitted for analysis. I am hoping to create a list of unique values from column A (Emails of those who submitted samples) and then use a sumif to count number of samples submitted from the data in column B. We would like to determine who our frequent fliers are for sample submissions and show it on a dashboard. I have included a screenshot below that shows the two columns A=submitter and B=#Samples, with the desired output being what we would have in a metric sheet housing the sum of samples by submitter for a bar chart. Any help would be greatly appreciated!

Ramzi K

@Cassie Walker

One way to do it -

=IF(COUNTIF(Submitter$1:Submitter@row, Submitter@row) <= 1, SUMIF(Submitter:Submitter, Submitter@row, [#Samples]:[#Samples]), "")

Pop this formula in [Desired output by name] column and copy it down the column. Should give you what you need.

I hope this helps,

Cheers,

Ramzi

Ramzi K

@Cassie Walker

One way to do it -

=IF(COUNTIF(Submitter$1:Submitter@row, Submitter@row) <= 1, SUMIF(Submitter:Submitter, Submitter@row, [#Samples]:[#Samples]), "")

Pop this formula in [Desired output by name] column and copy it down the column. Should give you what you need.

I hope this helps,

Cheers,

Ramzi

Cassie Walker

Ramzi--

Perfect! Thank you!! I entered this and it did exactly what I needed, and then I created a report to only display submitters with a value in the desired output column and used this to make a chart! thank you so much for your helpful and fast response! :) It will make my life a lot easier moving forward!

One final question @Ramzi K : What would I change in the syntax to allow it to be a column formula so I don't have to click and drag the formula down? Or would that mess up how it only happens at one instance of the duplicate?

Ramzi K

@Cassie Walker

Glad to help!

Unfortunately, due to the a direct cell reference (Submitter$1) you can't do that. If having a column formula is a hard requirement for you, I can try to accomplish what you need in a different way, but it will likely be more complicated.

Cheers,

Ramzi

Cassie Walker

@Ramzi K , ok I should be able to work with this. I am just not the primary user of the sheet, so I will make a reminder to go in and drag the formula down every so often. Thanks again!

Josh Wilson

@Ramzi K I just found this post from a few years ago and it's the closest I have come to solving my problem. How would you go about making this into a column formula?

Your formula worked great except I have about 6000 rows that I would need to drag the formula down to. And also, the source sheet gets updated weekly via a data shuttle.

Here is what I am trying to do: I am attempting to get a list of all client's that have 0 verified hours. Ultimately I would like to move a copy of a row or at least display on a separate sheet, a simple list of all clients that had 0 TOTAL verified hours for the date range. (For us, this means the client was not seen at all). This is an important compliance piece we are trying to solve with Smartsheet since the data is already being imported and available in another data shuttle import.