Google-sheets – Searching through sheets

google sheets

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?

Best Answer

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
enter image description here enter image description here

result
enter image description here

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.

Related Topic