Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Combine COUNTIF and SUM

edited 12/09/19 in Archived 2017 Posts




I have some topics a list were I need to combine COUNTIF and SUM function, but I get stuck.

I want the Total calculation (SUM-Function) at the bottom but i want the column only to be added if it fit's with the worker in Worker row.

Here I have a screenshot and calulated the total amount for Worker A and B manually but I need it to be calculated automatically. And it's possible that there will be more Topics, so I need the total of the hole column from top to bottom.



Thanks a lot for help!

2017-01-11 12_47_37-Test - Smartsheet.com_.png


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    The easiest thing to do is to move your totals to a different column.

    The reason for this is two fold:

    1. avoid circular references - that is having a formula in the [Jan] column calculate things in the [Jan] column.


    2. if you change "Worker A Total" to "Worker A" then you can use that to match the name in the sections above instead of trying to parse it out or (worse) hard coding it.


    Assuming you take that advice, here's the formula that would go somewhere to calculate the JAN total for Worker A. This formula is on the same row as where you currently have "Worker A Total" - and that text has been changed to "Worker A"


    =SUMIF(Worker:Worker, Worker23, JAN:JAN)


    My total row here is on row 23.


    Hope this helps.



This discussion has been closed.