Adding running totals

Phil Lomas

Hi. Sorry, this may seem really simple but I am new to smartsheets.

I have 2 columns. 1 - email addresses(email addresses) and another column sick days(number). I want to add all the sick days up as a running total for different peoples emails addresses but for the life of me cannot seem to get the formula right. Can anyone help? 

 

Thanks in advance :-)

Andrée Starå

Hi Phil,

Try something like this.

This is one way. Another would be to reference the email address instead from another column/cell. Which method would you prefer?

=SUMIF([Email Address]:[Email Address]; "email@email.com"; [Sick Days]:[Sick Days])

The same version but with the below changes for your and others convenience.

=SUMIF([Email Address]:[Email Address], "email@email.com", [Sick Days]:[Sick Days])

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Here's the other method.

The cell in the Contact@row column would be the email address to sum.

=SUMIF([Email Address]:[Email Address]; Contact@row; [Sick Days]:[Sick Days])

The same version but with the below changes for your and others convenience.

=SUMIF([Email Address]:[Email Address], Contact@row, [Sick Days]:[Sick Days])

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Phil Lomas

Hi Andree

That sort of helps but I have multiple email addresses in the email column so need it to differentiate and add the totals for different email addresses.

 

Andrée Starå

Ok.

What type of column is the email address column? Is it a contact column or a text column?

Phil Lomas

Hi Andree

It is a text column. Should I change it to a contact column?

Andrée Starå

No, you don't need to change it.

Try something like this instead.

=SUMIF([Email Address]:[Email Address]; FIND("email@email.com"; @cell) > 0; [Sick Days]:[Sick Days])

The same version but with the below changes for your and others convenience.

=SUMIF([Email Address]:[Email Address], FIND("email@email.com", @cell) > 0, [Sick Days]:[Sick Days])

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Phil Lomas

Hi Andre

That didn't work but I did change the column to 'contact' rather than text and it seems to now work.

I have another question where I need to trigger an email in the event that someone is off 5 times. i.e. 5 rows are generated with the same email address which will trigger an email.

Is this function possible?

Many thanks

Andrée Starå

Happy to help!

Yes, it's possible with a formula.

How many people do you need to check against?

Phil Lomas

Hi Andree

To set the scene there are 35 staff so  if they submit a sick form then it will show up on the list but I want it to trigger a notification if a member of staff is off on 5 separate occasions

Regards

Phil Lomas

Hi Andree

To set the scene there are 35 staff so if they submit a sick form then it will show up on the list but I want it to trigger a notification if a member of staff is off on 5 separate occasions. I have set up a separate column called 'sick on 5 or more occasions'  and thought this formula would work to count the number of times the email address appears in the column 'your email address' I could then set the trigger to send email based on this number is 5 or more. Problem is the formula does count the email addresses occurrences. Formula is '#incorrect argument set'

 =COUNTIF([Your email address]:[Your email address], "phil.lomas@company.org", >4)

Regards

Andrée Starå

You shouldn't use the >4 to count all.

Try this instead.

=COUNTIF([Your email address]:[Your email address], "phil.lomas@company.org")

Do you want to check a checkbox instead?

Hope that helps!

Did it work?

Phil Lomas

You are a star. It was so obvious I couldn't see it.  have removed the >4 and set up the automation and it works perfectly.

 

Thankyou

Andrée Starå

Thanks!

Happy to help!

It happens to us all!