Sumif formula error

Nickole Canfield

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!

Paul Newcome

@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.

Mark Cronk

Hi @Nickole Canfield ,

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

Paul Newcome

@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.

Nickole Canfield

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!

Paul Newcome

Happy to help. 👍️