0

I have trouble extracting data from a huge file. Until now I was using Vlookup but of course it only takes the first number it finds.

I need to be able to read data in each columns = X,Y,W,Z for the corresponding id.

Normally there is maximum duplicates but it happens that I have the same ID multiple times in the data tab.

Thus, I need to have a sum of all data in a column that corresponds to a specific ID.

In the image below, I need to pull out information for ID A.

I simplified the file. But normally I have hundreds of different ID's and they are all mixed up. enter image description here

Improve this question
2
  • New members commonly mistake this for a service site where we will do the work. It is a Q&A community where specific questions are asked after you have attempted something and get stuck. Please add details of what you have tried so far, including scripts, code or formulas, and we will try to help. If you need more info about asking questions, check out How to Ask in the help center.
    – CharlieRB
    Commented May 12, 2016 at 13:31
  • Just use AutoFilter to display the rows of interest, copy them, and paste them elsewhere.
    – Gary's Student
    Commented May 12, 2016 at 13:34

1 Answer 1

Reset to default
2

If your "X","Y","W","Z" columns are not in the same order as the output the formula you want is:

=SUMPRODUCT(($K$3:$K$5=$A2)*($L$2:$O$2=B$1)*$L$3:$O$5)

enter image description here

Pay special attention to what is and what is not an absolute reference.

If your columns are in the same order then use this, as it is not an array formula and will calculate faster with less overhead:

=SUMIF($K:$K,$A2,L:L)

Again paying attention to what is and what is not absolute.

Also this is exactly what Pivot tables are for:

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .