1

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?

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
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.

  • Thank you. This is perfect. Hopefully, when I have more reputation points, I will be able to come and upvote the answer. – alpha_989 May 31 '15 at 22:55
  • @alpha_989 I'm glad you returned and welcome on Web Applications !! – Jacob Jan Tuinstra Jun 1 '15 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.