I am writing here as a last resort, found similar tutorials and forum questions, but none of them isdoing the exact same thing (and I can’t/don’t know if/how its possible to combine different ideas in one formula/code).

Assume I have a table where I list all my furniture in the house, sorted by the rooms they are in. I need a command that will scan for an exact text in selected cell range (e.g. chair) and then sum the cell next to that (number that represents number of chairs in the room), so that I get as an output the total chairs in the house (5 in this case).

I will have over 50 items and 50 rooms and doing it manually seems obsolete.

CodePudding user response：

If the tables containing the figures are below each other, then you can select columns B:C then insert a pivot table. You can then drag and drop column D in row headers, then column C in the aggregate column.

CodePudding user response：

if everything is in the same columns as shown, then use the sumif() formula in the appropriate cell (I8 in this case). No need for vba.

=SUMIF(C[-6],"chair",C[-5])

CodePudding user response：

If the rooms are not all in the same column, you could use SUMPRODUCT: =SUMPRODUCT(--(A:E="chair"),B:F) In my example it checks the first 5 columns to equal the value chair and sums the value of the column next to it.