Forum Discussion
VLOOKUP PULLING TOTAL SUM FROM A MULTIPLE RAWS
Hello there. Need help pls.
I want to know, how I can pull out the total SUM of multiple rows by VLOOKUP. With VLOOKUP, I look up for the value (Example Invoice number ADL01) in sheet 1 and pull the Line value from sheet 2 belongs to ADL01 invoice number. But what if, sheet 2, has multiple rows with same Invoice number ADL01 and I want to pull the TOTAL SUM of those multiple rows in sheet 2 to sheet 1 with VLOOKUP? Any help pls. Thanks.
=SUMPRODUCT(($A$4:$A$13=A17)*$B$4:$B$13)Alternatives could be SUMPRODUCT, Power Query and a Pivot Table.
RohanAbaHK May be you want SUMIFS(). Try-
=SUMIFS($H$4:$H$15,$G$4:$G$15,B4)In case to refer sheet2 use-
=SUMIFS(Sheet2!$H$4:$H$15,Sheet2!$G$4:$G$15,B4)- Thank you for sharing. I think this works.
RohanAbaHK Not completely sure what you are aiming to do, but from my understanding look at the example below for a recruitment service.
candidate_name application_id assigned_value John A1 1 Cam A6 1 Simon A1 1 Jenny A3 1 The above table has distinct candidate_name in the 1st column, although there are multiple application_id. To get a total count of applications for each candidate, assign '1' as a value to each row on a new column (In this example the new column is assigned_value).
Use vlookup to and select the whole table in this case. It'll assign the correct application_id next to the candidate_name based on sheet1.
After that step, click on an empty cell anywhere and navigate to the 'data' tab. Then click on 'Consolidate' and select the entire table as in the above case we need to look up total applications for distinct candidates. Once you have selected the range, then click on 'add' within the 'consolidate' dialog box and then tick the 2 boxes, 'top row' and 'left column' and finally click on consolidate. Ensure to select 'sum' in the dialog box.
What this does is calculates the total applications for each distinct candidate. The top row contains the headers of the table and the left column is used to look up the total for each distinct customer.
It'll be more helpful if you could include an example of what it is you're trying to achieve with vlookup and provide an example as well. Hope this helps!
RohanAbaHK Then you can use SUMIF.
- thank you.
