Searching through sheets

Question

I need to search multiple sheets like this for certain words like #work and #hobbies and add up the numbers on the adjacent column.

https://docs.google.com/spreadsheets/d/1XRI6PtrTTIPK5wGj6cWaa9puWBjwdMimuxLnIp1sGFE/edit?usp=sharing

In this sheet, I need to add up the values in "Sheet 2" and "Sheet 3", for the cells in column B adjacent to the words #work and #hobbies. After I add them up, I want to put the total for #work and #hobbies in the cell adjacent to #work and #hobbies in "Sheet 1".

Essentially I want to assign "Sheet 1!B1"="Sheet 2!B1"+"Sheet 3!B3"

and

"Sheet 1!B4"="Sheet 2!B4"+"Sheet 3!B9"

Since I don’t know the location of these words, #work or #hobbies a priori, I need to search in column A, and find out the values and then add them up.

What code or function will help me evaluate this and do this job?

Answer 1

You can use the SUMIF formula to perform this type of calculation.

Formula

=SUMIF(Sheet2!A:A, A1, Sheet2!B:B) + SUMIF(Sheet3!A:A, A1, Sheet3!B:B)

Screenshot

Sheet2 & Sheet3

result

Explained

The first parameter of the SUMIF formula takes on the criterion range (column A in your example). The second parameter is the criterion itself. I needs to look for the entry in sheet 1 in column A: A1. The third parameter is the range to sum (if it differs from the criterion range), if the criterion is met.