You are here:

Excel/simultaneously search and sum up, update results in excel


QUESTION: HI, I have 2 sheets in an excel workbook. The first sheet, Sheet1, contains such columns:
(A1)Unit     (B1)Blue     (C1)Red      (D1)Black

The second sheet, Sheet2, contains such columns:
(A1)Unit     (B1)Blue     (C1)Red     (D1)Black
(A2)001        (B2) 1     (C2) 1      (D2) 0
(A3)001        (B3) 2     (C3) 1      (D3) 3
(A4)002        (B4) 2     (C4) 3      (D4) 1
(A100)099    (B100) 0     (C100) 2    (D100) 1

In Sheet2 for example, 001 came out twice. What I would like to do, if possible, is to automatically search every Unit number simultaneously, which at the same time will calculate the total number of Blue, Red and Black and then update the results in Sheet1. So that Sheet1 will look like this:

(A1)Unit     (B1)Blue     (C1)Red     (D1)Black
(A2)001      (B2) 3       (C2) 2      (D2) 3
(A3)002      (B3) 2       (C3) 3      (D3) 1
(A4)003      (B4) 1       (C4) 1      (D4) 1
(A100)099    (B100) 0     (C100) 2    (D100) 1

I am unsure on how to start writing the formula which will help me to automatically search simultaneously all the values in Sheet1's A column, in Sheet2, calculate the sums of each colour for each value and update the results in Sheet1.
The problem is because there are 100+ rows and therefore I want to know if it is possible to create a formula or make use of macros / VBA that will help to automate the results in every row without having to manually copy paste the formula in each row. Hope you can help me and thank you for your time!

ANSWER: Linify,

In B2 of Sheet1  =Sumif(Sheet2!$A:$A,$A2,B:B)
In C2 of Sheet1  =Sumif(Sheet2!$A:$A,$A2,C:C)
in D2 of Sheet1  =Sumif(Sheet2!$A:$A,$A2,D:D)

Then select B2:D2 and drag fill down the columns next to your data in column A.

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Hi based on the previous question, I would like to know, what if there are more than 5 sheets to refer to. How do I put in Sheet3, Sheet4, Sheet5 to the formula?


Sorry for leaving off the sheet2 name.  But glad you figured it out.

Here is how I would look at Sheet2, Sheet3, Sheet4, and Sheet5

In B2 of Sheet1  =Sumif(Sheet2!$A:$A,$A2,Sheet2!B:B)+Sumif(Sheet3!$A:$A,$A2,Sheet3!B:B)+Sumif(Sheet4!$A:$A,$A2,Sheet4!B:B)+Sumif(Sheet5!$A:$A,$A2,Sheet5!B:B)
In C2 of Sheet1  =Sumif(Sheet2!$A:$A,$A2,Sheet2!C:C)+Sumif(Sheet3!$A:$A,$A2,Sheet3!C:C)+Sumif(Sheet4!$A:$A,$A2,Sheet4!C:C)+Sumif(Sheet5!$A:$A,$A2,Sheet5!C:C)
in D2 of Sheet1  =Sumif(Sheet2!$A:$A,$A2,Sheet2!D:D)+Sumif(Sheet3!$A:$A,$A2,Sheet3!D:D)+Sumif(Sheet4!$A:$A,$A2,Sheet4!D:D)+Sumif(Sheet5!$A:$A,$A2,Sheet5!D:D)

Then drag fill down your columns

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.