Share via

Match a value from two lists or ranges sum the values

Anonymous
2021-09-27T23:01:09+00:00
TK 25 <br> --- --- <br> BN 75 <br> LF 10 <br> NE 90 <br> HP 20 <br> UG 45 <br> --- ---
HP 10
GD 15
--- ---
UG 25
--- ---
NE 5
--- ---
LF 67
--- ---
VT 90
--- ---
AS 100
--- ---
BN 55
--- ---
RW 42
--- ---
TK 25
BN 130
--- ---
LF 77
--- ---
NE 95
--- ---
HP 30
--- ---
UG 70
--- ---
--- ---

Sorry my dear friends,I couldn't question my issue properly.Consolidate and sum all values from 2 dynamic lists.

My list 1 contains 10 products and corresponding sales values,My list 2 contains 25 products and their sales values. Some of the products in both lists are similar nomenclature,I need a formula to get a consolidate list with sum values to get List3.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-10-04T10:15:25+00:00

    For a solution using formula(s), refer below image:

    Formula in cell H3 to be copied down:

    =IFERROR(IFERROR(INDEX(B$3:B$20, MATCH(0,INDEX(COUNTIF(H$2:H2,B$3:B$20)+(B$3:B$20=""),), 0)),INDEX(E$3:E$20, MATCH(0,INDEX(COUNTIF(H$2:H2,E$3:E$20)+(E$3:E$20=""),), 0))), "")

    For values, 2 options:

    Option 1: Enter below formula in cell I3 and copy down:

    =IF(H3="","",SUMIF(B$3:B$20,H3,C$3:C$20)+SUMIF(E$3:E$20,H3,F$3:F$20))

    Option 2: Enter below formula in cell J3 and copy down:

    =IF(H3="","",SUM(SUMIF(OFFSET(B$3,{0;0},{0;3},{6;9}),H3,OFFSET(C$3,{0;0},{0;3},{6;9}))))

    Regards

    Amit Tandon

    www.excelanytime.com/

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2021-10-01T08:47:05+00:00

    Hi,

    Feel free to post back if you have any other concerns.

    Best Regards,

    Mia

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-09-29T06:15:08+00:00

    You may download the file from below link. Power Query has been used for this.

    Consolidate2Tables

    Regards

    Amit Tandon

    www.excelanytime.com/

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-09-29T00:03:46+00:00

    Perhaps the following:

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-09-28T16:03:47+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.

    Match and Sum.

    Aiming for the most elegant and difficult.

    No formulas, no VBA macro.

    https://www.mediafire.com/file/6qm8wvasu2xe9et/09_28_21.xlsx/file

    https://www.mediafire.com/file/06ql3qx5bug85zg/09_28_21.pdf/file

    Was this answer helpful?

    0 comments