Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sum matching rows to another sheet

Posted on 2011-03-02
8
Medium Priority
?
295 Views
Last Modified: 2012-06-27
Hello Experts,

Here is what I have
SHEET 1
Name	ID	Pay
Mark	101	10
Phil	102	8
Dave	103	4
Mark	101	19
Dawn	104	4
Bob	105	60
Mark	101	7
Les	106	22
Dawn	104	31
Phil	102	40
Dave	103	11
Al	107	3
Les	106	12

Open in new window


I also have this in another sheet
SHEET 2
Name	ID     Pay  
Mark	101
Phil	102
Dave	103
Dawn	104
Bob	105
Les	106
Al	107

Open in new window


What I would like to do is fill in the Pay on SHEET 2 with the SUM of all the matching rows on SHEET1.  I assume a VLOOKUP on the ID would be best.
This is what SHEET2 will look like:
Name	ID	Pay
Mark	101	36
Phil	102	48
Dave	103	15
Dawn	104	35
Bob	105	60
Les	106	34
Al	107	3

Open in new window




I would like to know the simple formula that does it AND if there is a VB that will do it too.  I will use the formula, but I am learning VB and would like to see that too if possible.

THANKS!!!!
0
Comment
Question by:Mark Harris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35020621
Please check the attached example.

jppinto
Book1.xlsx
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35020632
This is the formula to put on the first row under Pay:

=SUMPRODUCT((Sheet1!$A$2:$A$14=Sheet2!A2)*(Sheet1!$B$2:$B$14=Sheet2!B2)*(Sheet1!$C$2:$C$14))

Then copy down the column.

jppinto
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 35021092
Would SUMIF be sufficient, I assume the IDs are unique. In sheet 2 C2 copied down

=SUMIF(Sheet1!B:B,B2,Sheet1!C:C)

regards, barry
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:jppinto
ID: 35021496
@fireman7147: did you at least took a look at my example?!? My formula works and gives the result that you wanted. I posted a working solution first and you accepted only the solution from barry?!

Please explain...
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35021643
With respect jppinto, why would you need SUMPRODUCT here, SUMIF is always preferable for a single condition? I note that fireman7147 asked for a "simple formula", and that's what I provided - the first solution posted isn't necessarily the best!

regards, barry
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35022685
Barry, with all due respect, you can use SUMIF here but you can also use SUMPRODUCT and it works. There are many ways of doing this. These are only two. I still think that at least my answer should be accepted as an "assist" because it works and I posted a working solution first. Maybe it's not the best solution, but still is a solution!

I would like to ear the explanation from the author here also...

regards,

jppinto
0
 

Author Comment

by:Mark Harris
ID: 35023582
@jppinto
Your solution worked indeed, but only if I had exactly the amount of rows in the example. Barry's solution worked with unlimited rows, which will be the case.

Sorry if you feel slighted, but his solution appeared to be the simplest and the most versatile. Any slight to you was unintentional.

Thanks Mark
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35025409
OK, thanks for your feedback. No hard feelings. Maybe next time I get it :)

regards,

jppinto
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question