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 |
+-------------------------------------------------------------------+

--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile: http://www.excelforum.com/member.php...o&userid=30661
View this thread: http://www.excelforum.com/showthread...hreadid=503268

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<attarkon.ee )



"shankarexcel"
wrote in message
news:shankarexcel.21xhzm_1137752702.9463@excelforu m-nospam.com...

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 |
+-------------------------------------------------------------------+

--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile:
http://www.excelforum.com/member.php...o&userid=30661
View this thread: http://www.excelforum.com/showthread...hreadid=503268

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


--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile: http://www.excelforum.com/member.php...o&userid=30661
View this thread: http://www.excelforum.com/showthread...hreadid=503268

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)

"Arvi Laanemets" wrote in message
...
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<attarkon.ee )



"shankarexcel"
wrote in message
news:shankarexcel.21xhzm_1137752702.9463@excelforu m-nospam.com...

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 |
+-------------------------------------------------------------------+

--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile:
http://www.excelforum.com/member.php...o&userid=30661
View this thread:
http://www.excelforum.com/showthread...hreadid=503268

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


--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile: http://www.excelforum.com/member.php...o&userid=30661
View this thread: http://www.excelforum.com/showthread...hreadid=503268

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"
wrote in message
news:shankarexcel.21xnbb_1137759608.2481@excelforu m-nospam.com...

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


--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile:
http://www.excelforum.com/member.php...o&userid=30661
View this thread: http://www.excelforum.com/showthread...hreadid=503268

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<attarkon.ee )



"shankarexcel"
wrote in message
news:shankarexcel.21xkzy_1137756603.5155@excelforu m-nospam.com...

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


--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile:
http://www.excelforum.com/member.php...o&userid=30661
View this thread: http://www.excelforum.com/showthread...hreadid=503268