SUM MULTIPLE VALUES IN A ROW BASED ON MULTIPLE CRITERIA

Julie Nelson

I am trying to sum the Total $ Amount of multiple items, when they meet the Account Code criteria of multiple values in a row (not the entire column). For example, in the first row, "TOTAL $ AMOUNT, 9810-420" should equal $550.00 and "TOTAL $ AMOUNT, 9810-430" should equal $100.00. The "Item... $ Amount" columns are text/number; the "Item... Account Code" columns are text/number, but they contain a JOIN formula.

I've tried multiple formulas, including SUMIFS, but all are returning #UNPARSEABLE.

=SUMIF([Item 1. $ Amount]@row, [Item 1. Account Code]@row = "9810-420") + SUMIF([Item 2. $ Amount]@row, [Item 2. Major-Minor]@row = "9810-420")

Thank you in advance for any suggestions!

Nick Korna

Hi @Julie Nelson,

Your SUMIF formulas are slightly incorrect as they should be checking the Item code, the code and then the amount:

So the formula would be:

=SUMIF([Item 1. Account Code]@row, "9810-420", [Item 1. $ Amount]@row) + SUMIF([Item 2. Account Code]@row, "9810-420", [Item 2. $ Amount]@row) + SUMIF([Item 3. Account Code]@row, "9810-420", [Item 3. $ Amount]@row)

Sample output based on supplied data:

Hope this helps, but if I've misunderstood something or you've any problems/questions just let us know!

JamesB

@Julie Nelson

here is the criteria for a SUMIF:

=SUMIF(Range,Criterion,[sum_range])

Range = Data to look at

Criterion = What to look for in the Range

Sum_Range = Where to get the value to sum

Try this formula

=SUM(SUMIF([Item 1.Account Code]@row, "9810-420", [Item 1.$ Amount]@row), +SUMIF([Item 2.Account Code]@row, "9810-420", [Item 2.$ Amount]@row))

Nick Korna

Hi @Julie Nelson,

Your SUMIF formulas are slightly incorrect as they should be checking the Item code, the code and then the amount:

So the formula would be:

=SUMIF([Item 1. Account Code]@row, "9810-420", [Item 1. $ Amount]@row) + SUMIF([Item 2. Account Code]@row, "9810-420", [Item 2. $ Amount]@row) + SUMIF([Item 3. Account Code]@row, "9810-420", [Item 3. $ Amount]@row)

Sample output based on supplied data:

Hope this helps, but if I've misunderstood something or you've any problems/questions just let us know!

Julie Nelson

@JamesB your formula resulted in #INCORRECT ARGUMENT.

=SUM(SUMIF([Item 1. Account Code]@row, "9810-420", [Item 1. $ Amount]@row, +SUMIF([Item 2. Account Code]@row, "9810-420", [Item 2. $ Amount]@row)))

@Nick Korna your formula resulted in #UNPARSEABLE still.

=SUMIF([Item 1. Account Code]@row, "9810-420", [Item 1. $ Amount]@row) + SUMIF([Item 2. Account Code]@row, "9810-420"), [Item 2. $ Amount]@row) + SUMIF([Item 3. Account Code]@row, "9810-420", [Item 3. $ Amount]@row)

Thoughts?

JamesB

@Julie Nelson

I used my formula in my sheet and did not get an error. Possibly my column names are not exactly the same as yours? Also what is your field type where you are placing your formula?

Nick Korna

#UNPARSEABLE is implying that your column headers don't match the ones in the formula so a copy/paste won't work.

When you try to edit the formula, what do the @row references look like? If they are coloured like this, then they should be OK:

If they are default black, then something is not matching and this is where the problem lies. For example, I've removed the . in Item 3's dollar amount which causes it to not be valid:

Can you check this? It's then a case of finding out which references are broken, which you can usually do by deleting a bit at a time until colour returns. For example, deleting the last criteria from the broken formula above:

This won't work (it's missing the SUM criteria), but it shows that the rest of the formula is valid.

Julie Nelson

@Nick Korna I reviewed the formula again and I had an extra parenthesis in it, which was causing the error. Your formula worked! Thank you so much for your help!

Nick Korna

No problem, glad to have helped! 🙂