software

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: www.excelforum.com/attachment.php?postid=4247 |
-------------------------------------------------------------------

--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile: www.excelforum.com/member.php...oamp;userid=30661
View this thread: www.excelforum.com/showthread...hreadid=503268Hi

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.laanemetslt;atgt;tarkon.ee )
quot;shankarexcelquot; gt;
wrote in message
news:shankarexcel.21xhzm_1137752702.9463@excelforu m-nospam.com...
gt;
gt; Hi,
gt;
gt; I have 3 columns Code(columnA), Subcode(columnB), Amount(columnC). I
gt; have created a columnD (Sum per Code) in which i have to add the amount
gt; with the previous amount in each cell till the subcode is same.
gt;
gt; I have used the following formula:
gt;
gt; =SUMPRODUCT((A3=A4)*(B3=B4),(C3 D2))
gt;
gt; I am getting the data correct in all cells in column D except the cells
gt; where subcode ends.
gt;
gt; I have attachd an excel sheet and highlighten in yellow where I get
gt; discrepancy. Can anyone help me?
gt;
gt; Thanks in advance
gt; Shankar
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: sample.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4247 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; shankarexcel
gt; ------------------------------------------------------------------------
gt; shankarexcel's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30661
gt; View this thread: www.excelforum.com/showthread...hreadid=503268
gt;

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: www.excelforum.com/member.php...oamp;userid=30661
View this thread: www.excelforum.com/showthread...hreadid=503268Seems 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)

quot;Arvi Laanemetsquot; gt; wrote in message
...
gt; Hi
gt;
gt; D2=SUMPRODUCT(--($A$2:$2=$A2),--($B$2:$B2=$B2),$C$2:$C2)
gt;
gt; (assuming data start from row 2, with row 1 being header row)
gt; , and copy D2 down
gt;
gt;
gt; --
gt; Arvi Laanemets
gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
gt;
gt;
gt;
gt; quot;shankarexcelquot; gt;
gt; wrote in message
gt; news:shankarexcel.21xhzm_1137752702.9463@excelforu m-nospam.com...
gt; gt;
gt; gt; Hi,
gt; gt;
gt; gt; I have 3 columns Code(columnA), Subcode(columnB), Amount(columnC). I
gt; gt; have created a columnD (Sum per Code) in which i have to add the amount
gt; gt; with the previous amount in each cell till the subcode is same.
gt; gt;
gt; gt; I have used the following formula:
gt; gt;
gt; gt; =SUMPRODUCT((A3=A4)*(B3=B4),(C3 D2))
gt; gt;
gt; gt; I am getting the data correct in all cells in column D except the cells
gt; gt; where subcode ends.
gt; gt;
gt; gt; I have attachd an excel sheet and highlighten in yellow where I get
gt; gt; discrepancy. Can anyone help me?
gt; gt;
gt; gt; Thanks in advance
gt; gt; Shankar
gt; gt;
gt; gt;
gt; gt; -------------------------------------------------------------------
gt; gt; |Filename: sample.zip |
gt; gt; |Download: www.excelforum.com/attachment.php?postid=4247 |
gt; gt; -------------------------------------------------------------------
gt; gt;
gt; gt; --
gt; gt; shankarexcel
gt; gt; ------------------------------------------------------------------------
gt; gt; shankarexcel's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=30661
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=503268
gt; gt;
gt;
gt;

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 quot;falsequot; as
output) and this formula works if I update manually the top most column
D2.

Thanks
Shankar--
shankarexcel
------------------------------------------------------------------------
shankarexcel's Profile: www.excelforum.com/member.php...oamp;userid=30661
View this thread: www.excelforum.com/showthread...hreadid=503268Sorry, 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)

quot;shankarexcelquot; gt;
wrote in message
news:shankarexcel.21xnbb_1137759608.2481@excelforu m-nospam.com...
gt;
gt; Hi Bob, thanks for your kind reply.
gt;
gt; =IF(AND(A3=A2,B3=B2),D2 C3)
gt; -- this formula won't work for the row 14 and 26 (gives quot;falsequot; as
gt; output) and this formula works if I update manually the top most column
gt; D2.
gt;
gt; Thanks
gt; Shankar
gt;
gt;
gt; --
gt; shankarexcel
gt; ------------------------------------------------------------------------
gt; shankarexcel's Profile:
www.excelforum.com/member.php...oamp;userid=30661
gt; View this thread: www.excelforum.com/showthread...hreadid=503268
gt;
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.laanemetslt;atgt;tarkon.ee )
quot;shankarexcelquot; gt;
wrote in message
news:shankarexcel.21xkzy_1137756603.5155@excelforu m-nospam.com...
gt;
gt; Exellent Arvi,
gt;
gt; It's working great. Could you please explain that formula
gt; =SUMPRODUCT(--($A$2:$2=$A2),--($B$2:$B2=$B2),$C$2:$C2)
gt; in detail, it will be educative for me? I am new to excel.
gt;
gt; Thanks
gt; Shankar
gt;
gt;
gt; --
gt; shankarexcel
gt; ------------------------------------------------------------------------
gt; shankarexcel's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30661
gt; View this thread: www.excelforum.com/showthread...hreadid=503268
gt;