I have a spreadsheet that has the below columns and rows. Under the Total Rebates header, I am looking for a formula that will look up the name in Row5/6ColA, search for it in Row 1 and add all values in row2 and row3 that match the name in row 5/6. Any suggestions?
------------A------------------------------B------------------------C-----------------------D
(1)Southwest Exteriors -------T&J Builders-----------T&J Builders-----------T&J Builders
(2) 1,5000.00---------------------1,500.00-----------------1,500.00------------1,500.00
(3) 1,000.00-----------------------1,000.00-----------------500.00 --------------750.00
(4)Vendor ---------------------------Total Rebates
(5)Southwest Exteriors -----------Need Formula Here
(6)T&J Builders---------------------Need Formual Here
Haven’t done one for a while, but the formula I believe you’re looking for is
HLOOKUP
So if you use the Excel help on that, it should give you some pointers.
I tried hlookup. It only lets you search one row, and it only pulls back the first # it finds, it doesnt sum up a range if numbers. If i used =Hlookup(A6,A1 : D2,2) would only return a value of $1500 and not $4500. Even if it did, it would leave out the second row of #'s.
=hlookup(a5,$a$1:$d$3,2,false)+hlookup(a5,$a$1:$d$3,3,false)
Lookups can be are difficult because multiples of the names occurrence in row one and I suspect that there may be more columns than you are showing.
If you have only a few columns you could write a hairy formula in B5 like -
if(A5=a1,sum(A$1:A$3),0) +
+if(A5=B1,sum(B$1:B$3),0)
+if(A5=C1,sum(C$1:C$3),0)
+if(A5=D1,sum(D$1: D$3),0)
+if(A5=E1,sum(E$1:E$3),0)
+if(A5=F1,sum(F$1:F$3),0)
That works for 5 columns and you could simply copy it to B6 for the next row.
For more columns though you can see how that approach is not very good
I’m still looking for a better solution
Didnt work. It solved the problem for including both rows, but it still only pulls back the first # it finds. If i put this formula into cell A6 for T&J, it returns the answer $2500(both #'s from col B) but does not add in columns C & D. It works for cell A5 because I only have 1 column with SW exteriors, but if I added Col E with SW Exteriors, the formula would not pull those #'s, just the ones from Col A.
Right now, there are only the 5 columns, but there will be plenty more in the near future. So, yea that would be very time consuming for numerous columns as the formulas would need to be updated everytime a new column is created.
B5:
=sumif($1:$1,$a5,$2:$2)+sumif($1:$1,$a5,$3:$3)
Thank You. This seems to be working.
Thanks to everyone who tried to figure this out.
No problem. Here’s another way to do the same thing:
B5:
=SUMPRODUCT(($1:$1=$A5)*($2:$2+$3:$3))
I think mbetter has the best answer. I was trying to figure out a SUMIFS solution based on the dimensions of the table and couldn’t quite get it where I wanted it.
Using a row by row approach works better and isn’t complicated by the width of the table.
It will, however suffer if the table length is expanded as it would require a sumif segment for each row of the table (or an additional segment in the sumproduct method).
I was just looking at some array type solutions for this in various search result locations. They looked more complicated that I would be willing to try.
It’s funny how this somewhat simple concept of summing from multiple areas gets quite complicated in practice.
Wow, I wasn’t even thinking about the possibility of adding rows. In that case, I’d modify my second idea thusly:
B5:
=SUMPRODUCT(($1:$1=$A6)*($2:$3))
If more rows are needed, the $2:$3 can be extended to $2:$4, $2:$5 and so on. Ace, if you’re still reading, I’d use this one.
Thanks Guys. There will not be any extra rows added, just columns, so the first solution you had works fine. I appreciate all the help and the quick responses.
[Roll the Ls voice]
BRILLIANT!
[ /Roll the ls voice]
Shows me that I need to get a better understanding of SUMPRODUCT applications.
Thanks for the demonstration mbetter
Can I ask why you have it set up that way? Things would be MUCH easier if you just did it like this:
Vendor Rebate Amount
Southwest Exteriors SE1 1500
Southwest Exteriors SE1 1000
T&J Builders TJB1 1500
T&J Builders TJB1 1000
T&J Builders TJB2 1500
T&J Builders TJB2 500
T&J Builders TJB3 1500
T&J Builders TJB3 750
No matter how many rebates you continue to add, a simple pivot chart does all the subtotaling you’ll ever want.
To expand on this, I created an expanded listing. The first chart below is your data, the second is a pivot chart (which is very simple to put together). You don’t have to have the rebate code I made up - just thought that might help since it looks like you want to see each individual rebate.
Vendor Rebate Amount
Southwest Exteriors SE1 1500
Southwest Exteriors SE1 1000
Southwest Exteriors SE2 750
Southwest Exteriors SE2 1000
Southwest Exteriors SE3 2000
Southwest Exteriors SE3 500
T&J Builders TJB1 1000
T&J Builders TJB1 2000
T&J Builders TJB1 1500
T&J Builders TJB2 750
T&J Builders TJB2 500
T&J Builders TJB2 250
T&J Builders TJB2 1250
T&J Builders TJB3 300
T&J Builders TJB4 754
T&J Builders TJB4 167
T&J Builders TJB4 615
T&J Builders TJB5 1000
T&J Builders TJB6 1500
ABC Siding ABC1 1111
ABC Siding ABC1 875
ABC Siding ABC1 450
ABC Siding ABC1 300
ABC Siding ABC2 100
ABC Siding ABC2 2000
ABC Siding ABC3 1500
ABC Siding ABC3 750
ABC Siding ABC3 500
ABC Siding ABC3 250
ABC Siding ABC4 1250
ABC Siding ABC4 300
ABC Siding ABC4 754
Row Labels Sum of Amount
ABC Siding $10,140
ABC1 $2,736
ABC2 $2,100
ABC3 $3,000
ABC4 $2,304
Southwest Exteriors $6,750
SE1 $2,500
SE2 $1,750
SE3 $2,500
T&J Builders $11,586
TJB1 $4,500
TJB2 $2,750
TJB3 $300
TJB4 $1,536
TJB5 $1,000
TJB6 $1,500
Grand Total $28,476
Sorry, last one. If you don’t want the individual rebate subtotals in there, it’s a click away from being:
Row Labels Sum of Amount
ABC Siding $10,140
Southwest Exteriors $6,750
T&J Builders $11,586
Grand Total $28,476