Sum contract invoices by supply category where multiple suppliers in the category

[IKH104]

I want to run a formula to look up a contract ref and run sum values for supplier invoices depending on their supply category as eg below - please advise formula - I reckon combo of vlookup, sumif, sumproduct???

Input		Output
Customer Contract Ref	Invoice Ref	Value	Supplier		Fruits	Veg		Customer Contract Ref	Fruits	Veg
10001	1	5000	Supplier A		Supplier A	Supplier B		10001	7000	300
10001	2	300	Supplier B		Supplier C			10002	7000	400
10001	3	2000	Supplier C		Supplier D			10003	1300	600
10002	4	400	Supplier B
10002	5	7000	Supplier A
10003	6	300	Supplier A
10003	7	100	Supplier A
10003	8	900	Supplier D
10003	9	600	Supplier B

<tbody>
</tbody>

 

[adam087]

Hi, good afternoon

Welcome to the board

This formula will work (copy it across and down), but there may be a more elegant way as my use of OFFSET is a little clumsy I fear. But should be good to get you going. You will need to change the references to suit, and ensure they all line up correctly.

Excel 2010

	A	B	C
12	Customer Contract Ref	Fruits	Veg
13	10001	7000	300
14	10002	7000	400
15	10003	1300	600

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B13 {=SUM($C$2:$C$10*($A$2:$A$10=$A13)*(COUNTIF(OFFSET($F$1, 1, MATCH(B$12, $F$1:$G$1, 0)-1, 1000, 1), $D$2:$D$10)>0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope that helps.

/AJ

 

[steve the fish]

This should do it:

=SUMPRODUCT(SUMIFS($C$2:$C$10,$A$2:$A$10,$I2,$D$2:$D$10,F$2:F$4))

 

[Aladin Akyurek]

J2, copy across and down:

=SUM(INDEX(SUMIFS($C$2:$C$10,$D$2:$D$10,INDEX($F$2:$G$4,0,
  MATCH(J$1,INDEX($F$1:$G$1,1,0),0)),$A$2:$A$10,$I2),0))

 

[IKH104]

Sumproduct / Sumif works well - multiple sumifs is new to me!