I have 2 sheets (Sheet 1 and Sheet 2) with large set of data, where I have to sum based on a criteria (Line Items in Column B), as this is the only column that is matching in both sheets. How do i Solve this?. I have used the following formula, however it is giving me an error. In sheet 2, with the help of a formula, I need to sum up based on line item and drag it down. Can someone plz help?

=SUMIFS(INDEX(Sheet1!C2:E10,MATCH(B2,Sheet1!B2:B10,0)),Sheet1!C2:E10,B2)

Sheet1:

** Sheet2:
**
| P/L      | Line Items | Jan  | Feb | March |
|----------|------------|------|-----|-------|
| Sales    | 5001       | 6792 |     |       |
| Scrap    | 5210       |      |     |       |
| Supplies | 6300       |      |     |       |
|          |            |      |     |       |
|          |            |      |     |       |
|          |            |      |     |       |
|          |            |      |     |       |
|          |            |      |     |       |
|          |            |      |     |       |


Thanks,
Ayesha

=SUMIFS(INDEX(Sheet1!C2:E10,MATCH(B2,Sheet1!B2:B10,0)),Sheet1!C2:E10,B2)

CodePudding user response：

You may use SUMPRODUCT combined with unary operators and MATCH to get this:

Formula in cell K2 is:

=SUMPRODUCT(--($B$2:$B$10=$J2)*--(COLUMN($C$2:$E$10)=MATCH(K$1,$C$1:$E$1,0) 2)*$C$2:$E$10)

Just drag to right and down

Assume your data start from A1,

=LAMBDA(KEYS,DATA,ITEMS,MONTHS,
 MAKEARRAY(COUNTA(ITEMS),COUNTA(MONTHS),LAMBDA(ROW,COL,
  SUM(FILTER(INDEX(DATA,,COL),KEYS=INDEX(ITEMS,ROW),0))
 ))
)(B2:B10,C2:E10,G2:G6,H1:J1)

1. Name the ranges you are working on with LAMBDA(),
2. Use MAKEARRAY() to define the ROW and COL index,
3. FILTER() the DATA according to ITEMS,
4. SUM() the result of FILTER().

If you are not familiar with Array Formulas, you can also use this instead:

=SUM(FILTER(C$2:C$10,$G2=$B$2:$B$10,0))

Put it into H2 (of my example) and darg it left to right, top to bottom, should also work.