Adding multiple columns if one column equals X

Christie Hart

I have a multi column sheet. If column Alone= CN, I want to total four columns.

=SUMIF([$ Regular]:[$ Regular], [$$ xyz 2021]:[$$ xyz 2021], [Per Item $ 2021]:[Per Item $ 2021], [null 2021]:[null 2021], [Revised $$]:[Revised $$ ], (Alone:Alone, CONTAINS("CN", @cell))

I've tried ever iteration and I cannot figure it out. Any help is greatly appreciated

Mark Cronk

Try:

=SUMIFS([$ Regular]:[$ Regular], ISNUMBER(@cell), [$$ xyz 2021]:[$$ xyz 2021], ISNUMBER(@cell), [Per Item $ 2021]:[Per Item $ 2021],ISNUMBER(@cell), [null 2021]:[null 2021],ISNUMBER(@cell), [Revised $$]:[Revised $$ ],ISNUMBER(@cell), Alone:Alone, @cell="CN")

Mark

Christie Hart

Thanks for the quick reply. Its still not working. I'm getting an "incorrect argument set"

Mark Cronk

I read your post again and I think I misunderstood. You want to sum each column if Alone =CN. Try this:

=SUMIF([$ Regular]:[$ Regular], Alone:Alone, "CN")+ SUMIF([$$ xyz 2021]:[$$ xyz 2021], Alone:Alone, "CN") +SUMIF([Per Item $ 2021]:[Per Item $ 2021],Alone:Alone, "CN") +SUMIF( [null 2021]:[null 2021], Alone:Alone, "CN") +SUMIF( [Revised $$]:[Revised $$ ], Alone:Alone, "CN")

Work?

Mark

Christie Hart

I think we may be closer. I also think I may not be explaining properly.

If Alone=CN in that row, I want to add those numbers in each of those columns, and then add them all together into one number.

Mark Cronk

Using the columns in your last post, this should be the formula:

=SUMIF([two words]:[two words], Alone:Alone, "CN")+ SUMIF([one word]:[one word], Alone:Alone, "CN") +SUMIF([three words]:[three words],Alone:Alone, "CN") +SUMIF( [too many words]:[too many words], Alone:Alone, "CN")

Mark

Christie Hart

Mark, thanks for all of your help! I must have something keyed wrong. I'm still getting an Unparseable error.

Paul Newcome

The syntax is off. You are using the SUMIFS syntax with a SUMIF function. Easiest way to fix this would be to add an S to the end of each function so that they are all SUMIFS.

Christie Hart

Paul, thanks for the help. Still getting that dreaded error!

Paul Newcome

Make sure the column names match what are actually in your sheet.

=SUMIFS([two words]:[two words], Alone:Alone, "CN") + SUMIFS([one word]:[one word], Alone:Alone, "CN") + SUMIFS([three words]:[three words],Alone:Alone, "CN") + SUMIFS([too many words]:[too many words], Alone:Alone, "CN")

Mark Cronk

Thanks for the fix Paul.

Mark

Paul Newcome

@Mark Cronk Sure thing.