# Sumif formula error

Options

### I am getting the #INCORRECT ARGUMENT SET error on my formula and can't figure out why. I am assuming it has something to do with the range, but not sure. Below is the current formula I have @Paul Newcome

=SUMIF({Program Months Active}, =Date@row, {Monthly Cost})

What I am trying to do is search in the range "Program Months Active" (which is about 42 columns and 65 rows in a separate sheet), and if the "date@row" that is on the current sheet is found in any of those rows/columns, to add up what is in the rows of the "Monthly Cost" column that contain that date (same sheet as the "program monthly active"). I'm not sure if I need to use a contains or index function in there somewhere or not. There are checkboxes in the range but I don't believe that impacts the formula error. Below are images of the sheet with the formula as well as the reference sheet. I appreciate any help!

• ✭✭✭✭✭✭
Options

@Mark Cronk Thanks for the shout-out! Your thought process is on the right track. The problem is not necessarily a multi-column range within the function but that the two ranges do not match in size and shape. If the {Monthly Cost} range was also 42 columns by 65 rows, then the formula would actually work.

Your solution should work, but with 21 columns to reference, that can become rather cumbersome and unwieldy pretty quickly.

There are a couple of ways we could approach this that would allow for shorter formulas. The one I am leaning towards involves a helper column (both of the text/number type) on each of the sheets. Basically we are going to convert the dates into text strings and use a CONTAINS function to do the searching.

Data Sheet Helper Column:

=JOIN(COLLECT([Month 1]@row:[Last Date Column]@row, [Month 1]@row:[Last Date Column]@row, ISDATE(@cell)), ",")

This will pull all of the dates into a text string with a comma delimiter.

Metrics Sheet Helper Column:

=Date@row + ""

This will convert the date into a text string.

Formula to sum:

=SUMIFS({Monthly Cost}, {Data Sheet Helper Column}, CONTAINS([Metrics Sheet Helper Column]@row, @cell))

@Nickole Canfield Please note: I did use a SUMIFS instead of a SUMIF. That is just personal preference, but they do have slightly different syntax.

• ✭✭✭✭✭✭
Options

I think you're getting the error because you're using a multi-column range in a SUMIF formula.

To do this you need to use 12 different ranges, one for each month. Your formula would be:

=SUMIF({month 1},date@row, {monthly cost})+SUMIF({month 2},date@row, {monthly cost})+SUMIF({month 3},date@row, {monthly cost})+SUMIF({month 4},date@row, {monthly cost})+.....you get the idea.

Maybe Paul has a better solution. He's the master.

Good luck,

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
Options

@Mark Cronk Thanks for the shout-out! Your thought process is on the right track. The problem is not necessarily a multi-column range within the function but that the two ranges do not match in size and shape. If the {Monthly Cost} range was also 42 columns by 65 rows, then the formula would actually work.

Your solution should work, but with 21 columns to reference, that can become rather cumbersome and unwieldy pretty quickly.

There are a couple of ways we could approach this that would allow for shorter formulas. The one I am leaning towards involves a helper column (both of the text/number type) on each of the sheets. Basically we are going to convert the dates into text strings and use a CONTAINS function to do the searching.

Data Sheet Helper Column:

=JOIN(COLLECT([Month 1]@row:[Last Date Column]@row, [Month 1]@row:[Last Date Column]@row, ISDATE(@cell)), ",")

This will pull all of the dates into a text string with a comma delimiter.

Metrics Sheet Helper Column:

=Date@row + ""

This will convert the date into a text string.

Formula to sum:

=SUMIFS({Monthly Cost}, {Data Sheet Helper Column}, CONTAINS([Metrics Sheet Helper Column]@row, @cell))

@Nickole Canfield Please note: I did use a SUMIFS instead of a SUMIF. That is just personal preference, but they do have slightly different syntax.

• Options

Thank you both for your help @Mark Cronk and @Paul Newcome ! I will try the helper column and reach out if I have any further questions!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!