Combining Vlookup Hlookup and SUMIF (or with array Sum(if...

C

CA_User

Board Regular
Joined
Nov 5, 2004
Messages
126
I was reading the comments from the below topic and was very close to answering the question I had... but I need it to go a step further.

http://www.mrexcel.com/board2/viewtopic.php?t=249283&highlight=combine+hlookup+vlookup


I have a table with the following layout:

1) Column A has an ID # (with same ID appearing on different rows)
2) Row one is the heading with a letter and can repeat across multiple columns.

I want to create a sumif using a vlookup and a hlookup, but I don't know how to combine the two and get the formula to work. For example in the list below, I want to sum everything that has ID6 and the columns with a B header.... I know this can be done using pivot tables, but I am trying to avoid this.........Please help! thanks

ID# A A B C A B
ID1 8 5 1 1 4 9
ID2 9 0 2 5 9 9
ID6 9 1 2 8 2 8
ID4 2 7 9 0 8 0
ID5 4 7 5 5 6 9
ID6 1 9 2 7 7 8
ID7 4 4 8 5 5 0
ID8 5 0 6 9 0 2
ID6 8 1 3 9 4 0
ID2 6 5 9 5 4 4
ID1 8 8 1 1 5 5
ID6 6 1 7 8 1 9
ID6 1 8 1 0 3 4
ID2 3 0 6 7 7 6
ID1 9 1 5 4 1 0
ID2 9 9 2 2 1 2
ID1 8 9 4 5 6 7
ID2 9 0 7 1 4 2
ID6 6 9 0 9 1 6
ID2 7 8 3 8 3 2
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Hi
Hlookup and vlookup returns only the first occurance of the search term. what you are lookinf for is to sum all values wherever ID6 occurs in col A and B occurs in row 1.
If you have no aversion to macros, here is a solution. Paste the following codes in the macro window ( Alt F8)

Code: 
m = InputBox("enter ID number")
n = InputBox("enter row character")
Cells(1, 1) = ""
x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To x
For b = 2 To y
If Cells(a, 1) = m And Cells(1, b) = n Then
Cells(1, 1) = Cells(1, 1) + Cells(a, b)
End If
Next b
Next a
run the macro. enter search terms ID6 and B respectively. The total will be displayed in cell A1
Ravi
 
Upvote 0
Can I do this via a formula and not a macro? Perhaps some combination of Sum, Index and match? ( i just don't know how to make that combination)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA Code Help
Replies
4
Views
267
Peter_SSs
Peter_SSs
K
  • Question
Fill any five cell only
Replies
3
Views
175
Peter_SSs
Peter_SSs
J
  • Question
help with combinations
Replies
3
Views
214
skillilea
S
SaraWitch
  • Solved
Add zero to beginning of number extraction
2
Replies
10
Views
353
MARK858
MARK858
J
  • Solved
IFS
Replies
4
Views
88
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,571
Messages
6,120,302
Members
448,954
Latest member
EmmeEnne1979

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top