Conditional Sum
 

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.

Conditional Sum
 

Check out Mr. Excel's site on array formula's

http://www.mrexcel.com/td0128.html

HTH

Die_Another_Day

Tarique wrote:
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.

Conditional Sum
 

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

"Die_Another_Day" wrote:

Check out Mr. Excel's site on array formula's

http://www.mrexcel.com/td0128.html

HTH

Die_Another_Day

Tarique wrote:
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.

Conditional Sum
 

This formula should work.

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


"Tarique" wrote:

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.

Conditional Sum
 

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[b]

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tarique" wrote in message
...
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.