SUM by Month and a task type

Pam Dunn

I want to create a summary sheet and show totals by month by a certain column

Metrics Report

Main Smartsheet

=SUMIFS({CopyWritten}, $[Primary Column]@row, {Monthcompleted}, Jun$1) = 0, "", SUMIFS({CopyWritten}, ${Primary Column]@row, {Monthcompleted}, Jun$1)), "")

The error I'm getting is #unparseable

Then I want to break it down by week as well.

Mark Cronk

In your JAN column use:

=SUMIFS({CopyWritten}, [Primary Column]@row, {Monthcompleted}, 1)

Change the 1 to 2 for Feb, 3 for Mar and so on. You'll need a criteria for year if you're keeping multiple years in your Main Sheet.

Your CopyWritten range values have to equal your Copy Written values in your summer sheet. Your screenshot shows numbers in one and text in the other.

Still getting an error?

Mark

Pam Dunn

@Mark Cronk Hi Mark just getting back to this. It is still giving an error now it is #Incorrect Argument Set. Also how would I incorporate multiple years?

Mark Cronk

Hi Pam,

Not sure why you're getting an error. Can you attach screen shots of both sheets and the formula you're using?

To incluse a year you'll add another range and criteria to your SUMIFS. Add: , {date}, Year(@cell)= "2021") for just 2021 dates.

Mark

Pam Dunn

Hi Mark,

Here is my source document. I want to total the #'s from Copywritten column and show a total in the summary sheet by the month. The total should be 181.

Formula is that you provided.: =SUMIFS({Copywritten}, [Primary Column]@row, {Monthcompleted}, 6)

The below formula kind of works I don't get an error but it brings back nothing = 0, "", when the total should be 181

=IF(COUNTIFS({Copywritten}, $[Primary Column]@row, {Monthcomplete}, Jun$1) = 0, "", COUNTIFS({Copywritten}, $[Primary Column]@row, {Monthcomplete}, Jun$1))

I believe I should be using SUMIFS as the information in the column are numbers. When I change the above formula to SUMIFS I get #Incorrect Argument Set error

Here is the image of the summary sheet

I am using a summary sheet that was provided in the Marketing Request Management template set and amending to fit my data. It is called Metrics Sheet - Over Time. https://app.smartsheet.com/b/publish?EQBCT=e19396b2c1914f3780865999eb5f2df6

Here is the formula it is using I don't have a status column so I eliminated that from the formula.

=IF(COUNTIFS({IntakeStatus}, $Status@row, {IntakeType}, $[Primary Column]@row, {IntakeCompleteMonth}, Jan$1) = 0, "", COUNTIFS({IntakeStatus}, $Status@row, {IntakeType}, $[Primary Column]@row, {IntakeCompleteMonth}, Jan$1))

Mark Cronk

Hi Pam,

You're correct. Since copywriter is a contact list you'll use COUNTIFS. However to get a match your primary column also needs to be a contact list. It doesn't appear to be.

Mark

Pam Dunn

@Mark Cronk I still don't have a resolution the formula is not working I am still getting errors or a blank field using any of these 3 formulas I noted in my last message. I am not counting the copywriter column I want to total the number in the copy written column by month completed and have it appear on another sheet.

Pam Dunn

@Mark Cronk ok I was overcomplicating my formula. I eliminated the [Primary Column]@row since I only need to total one row and determine the month. I think I am good to go. Thanks

Mark Cronk

Hi Pam,

Glad you found your solution. Thank you for contributing to the community.

Mark