How to add values in a range of cells if neighboring cell has a specific string value only those cel-CodePudding

If the category matches the categories string value, how do I add the amount next to the category to the Totals for the categories legend

So far I've tried using the SUMIF() function.

CodePudding user response：

Try SUMIFS().

=SUMIFS(Table1[Amount],Table1[Category],[@Category])

CodePudding user response：

Try this:

Where the Excel table on the left is named as Table3. You get #SPILL! because you are using the formula like this:

=SUMIF(Table3[Category],[Categories],Table3[Amount])

You forgot to include @ that means the category value of a given row. Using [Categories] will generate an array and it is not supported for Excel Tables so you get #SPILL!. Check the following information:

Using structured references with Excel tables.
#SPILL! error - Table formula