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??? [TABLE="width: 943"]
<tbody>[TR]
[TD="colspan: 7"]Input
[/TD]
[TD][/TD]
[TD="colspan: 3"]Output
[/TD]
[/TR]
[TR]
[TD]Customer Contract Ref
[/TD]
[TD]Invoice Ref
[/TD]
[TD]Value
[/TD]
[TD]Supplier
[/TD]
[TD][/TD]
[TD]Fruits
[/TD]
[TD]Veg
[/TD]
[TD][/TD]
[TD]Customer Contract Ref
[/TD]
[TD]Fruits
[/TD]
[TD]Veg
[/TD]
[/TR]
[TR]
[TD]10001
[/TD]
[TD]1
[/TD]
[TD]5000
[/TD]
[TD]Supplier A
[/TD]
[TD][/TD]
[TD]Supplier A
[/TD]
[TD]Supplier B
[/TD]
[TD][/TD]
[TD]10001
[/TD]
[TD]7000
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]10001
[/TD]
[TD]2
[/TD]
[TD]300
[/TD]
[TD]Supplier B
[/TD]
[TD][/TD]
[TD]Supplier C
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]10002
[/TD]
[TD]7000
[/TD]
[TD]400
[/TD]
[/TR]
[TR]
[TD]10001
[/TD]
[TD]3
[/TD]
[TD]2000
[/TD]
[TD]Supplier C
[/TD]
[TD][/TD]
[TD]Supplier D
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]10003
[/TD]
[TD]1300
[/TD]
[TD]600
[/TD]
[/TR]
[TR]
[TD]10002
[/TD]
[TD]4
[/TD]
[TD]400
[/TD]
[TD]Supplier B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10002
[/TD]
[TD]5
[/TD]
[TD]7000
[/TD]
[TD]Supplier A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10003
[/TD]
[TD]6
[/TD]
[TD]300
[/TD]
[TD]Supplier A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10003
[/TD]
[TD]7
[/TD]
[TD]100
[/TD]
[TD]Supplier A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10003
[/TD]
[TD]8
[/TD]
[TD]900
[/TD]
[TD]Supplier D
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10003
[/TD]
[TD]9
[/TD]
[TD]600
[/TD]
[TD]Supplier B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

<tbody>
[TD="align: center"]12[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Customer Contract Ref[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Fruits[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Veg[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]10001[/TD]
[TD="align: center"]7000[/TD]
[TD="align: center"]300[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]10002[/TD]
[TD="align: center"]7000[/TD]
[TD="align: center"]400[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]10003[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]600[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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!