Sum with multiple conditions

[shankarexcel]

Hi,

I have 3 columns Code(columnA), Subcode(columnB), Amount(columnC). I
have created a columnD (Sum per Code) in which i have to add the amount
with the previous amount in each cell till the subcode is same.

I have used the following formula:

=SUMPRODUCT((A3=A4)*(B3=B4),(C3+D2))

I am getting the data correct in all cells in column D except the cells
where subcode ends.

I have attachd an excel sheet and highlighten in yellow where I get
discrepancy. Can anyone help me?

Thanks in advance
Shankar


+-------------------------------------------------------------------+
|Filename: sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4247 |
+-------------------------------------------------------------------+

 

[Arvi Laanemets]

Hi

D2=SUMPRODUCT(--($A$2:$2=$A2),--($B$2:$B2=$B2),$C$2:$C2)

(assuming data start from row 2, with row 1 being header row)
, and copy D2 down


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"shankarexcel" <[email protected]>
wrote in message
news:[email protected]...

 

[shankarexcel]

Exellent Arvi,

It's working great. Could you please explain that formula
=SUMPRODUCT(--($A$2:$2=$A2),--($B$2:$B2=$B2),$C$2:$C2)
in detail, it will be educative for me? I am new to excel.

Thanks
Shankar

 

[Bob Phillips]

Seems a bit OTT to me. Why not just add

=IF(AND(A3=A2,B3=B2),D2+C3)

to D3 and copy down?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[shankarexcel]

Hi Bob, thanks for your kind reply.

=IF(AND(A3=A2,B3=B2),D2+C3)
-- this formula won't work for the row 14 and 26 (gives "false" as
output) and this formula works if I update manually the top most column
D2.

Thanks
Shankar

 

[Bob Phillips]

Sorry, my bad. I corrected in my test but posted the old version. Should be

=IF(AND(A3=A2,B3=B2),D2+C3,C3)


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"shankarexcel" <[email protected]>
wrote in message
news:[email protected]...

 

[Arvi Laanemets]

Hi

The formula sums all amounts with given code and subcode in range from 1st
datarow to current row (the one with formula in it) in table.

The right use of absolute (preceeded with $) and relative (not preceeded
with $) references guarantees, that the referred range will be adjusted
automatically when the formula is copied.

A double unary conversion (--LogicalExpression) returns TRUE/FALSE check
results to their numeric equivalents (1/0). You can have the formula in form
=SUMPRODUCT(($A$2:$2=$A2)*($B$2:$B2=$B2)*($C$2:$C2))
too, but the unary conversion will suppesedly work somewhat faster - it will
count when you have a lot of such formulas on sheet.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"shankarexcel" <[email protected]>
wrote in message
news:[email protected]...