Conditional Sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have take so much time for conditional sum and try different conditions but
in vain.
Please tell me the solution.
e.g:
Distributor Name Article NO. Sale
ABC Agency 701 15
ABC Agency 709 20
ABC Agency 1501 55
XYZ Traders 701 35

I want to sum the sales numbers by Article wise 701 & 1501 in same cell for
same distributor.
Please help me to solve this likly to cronic situation.
 
Entered as an array formula with Ctrl+Shift+enter

=SUM(IF(((B2:B5=701)+(B2:B5=1501))*(A2:A5="ABC Agency"),C2:C5))

OR

=SUMPRODUCT(--(A2:A5="ABC
Agency"),--(B2:B5=701),(C2:C5))+SUMPRODUCT(--(A2:A5="ABC
Agency"),--(B2:B5=1501),(C2:C5))

You can substiute the constants with cells containing your values

HTH
 
This formula should work.

=SUMPRODUCT((B2:B5=701)+(B2:B5=1501),--(A2:A5="ABC Agency"),C2:C5)
 
You could try this:

=SUMPRODUCT((A2:A100="ABC Agency")*(B2:B100={701,1501})*C2:C100)

You could, however, designate particular cells to contain your variable
criteria, so that you could make changes, without having to revise the
formula itself.

Say D1 = Distributor,
D2 = Article Number[A]
D3 = Article Number

You could leave one of the "Article" cells empty, if you only wished to
total one of them at a time.

=SUMPRODUCT((A2:A100=D1)*((B2:B100=D2)+(B2:B100=D3))*C2:C100)
 
Back
Top