Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum Up Numbers w/o Breaks
Heres kinda how my spreadsheet looks: Code: -------------------- A B C D 1. Disc Cost Adj Result 2. Sample1 $1.00 -1 -$1.00 3. Sample2 $5.00 -5 -$25.00 4. Sample3 $1.00 -1 -$1.00 5. 6. Sample1 $1.00 -1 -$1.00 7. Sample1 $1.00 -1 -$1.00 8. -------------------- What I need is all the cells in row "D" to multiply C*B (easy), but if there is nothing in C, then I need it to sum up the values in D (from 1 above it to the next break in numbers). So D5 would = -$27.00, and D8 would = -$2.00. This is what I have come up with so far (D5): Code: -------------------- =IF(C5="",IF(C4="","", ???),C5*B5) -------------------- Any ideas? -- c_schear ------------------------------------------------------------------------ c_schear's Profile: http://www.excelforum.com/member.php...o&userid=16494 View this thread: http://www.excelforum.com/showthread...hreadid=278720 |
#2
|
|||
|
|||
c_schear wrote... Heres kinda how my spreadsheet looks: A B C D 1 Disc Cost Adj Result 2 Sample1 $1.00 -1 -$1.00 3 Sample2 $5.00 -5 -$25.00 4 Sample3 $1.00 -1 -$1.00 5 6 Sample1 $1.00 -1 -$1.00 7 Sample1 $1.00 -1 -$1.00 8. What I need is all the cells in row "D" to multiply C*B (easy), but if there is nothing in C, then I need it to sum up the values in D (from 1 above it to the next break in numbers). So D5 would = -$27.00, and D8 would = -$2.00. . . . ... If you want the same formulas for all cells in column D from cell D2 down, try D2: =IF(ISNUMBER(C2),B2*C2,SUM(INDIRECT("R"& LOOKUP(2,1/(1-ISNUMBER(C$1:C1)),ROW(C$1:C1)+1)&"C:R[-1]C",0))) filled down as far as needed. -- hgrove ------------------------------------------------------------------------ hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=278720 |
#3
|
|||
|
|||
Hi
try the following formulas D1: =B1*C1 D2: =IF($C2<"",$B2*$C2,SUM(OFFSET($D1,0,0,-ROW()+1+SUMPRODUCT(MAX(($C$1:$C 1="")*ROW($C$1:$C1)))))) and copy this down -- Regards Frank Kabel Frankfurt, Germany "c_schear" schrieb im Newsbeitrag ... Heres kinda how my spreadsheet looks: Code: -------------------- A B C D 1. Disc Cost Adj Result 2. Sample1 $1.00 -1 -$1.00 3. Sample2 $5.00 -5 -$25.00 4. Sample3 $1.00 -1 -$1.00 5. 6. Sample1 $1.00 -1 -$1.00 7. Sample1 $1.00 -1 -$1.00 8. -------------------- What I need is all the cells in row "D" to multiply C*B (easy), but if there is nothing in C, then I need it to sum up the values in D (from 1 above it to the next break in numbers). So D5 would = -$27.00, and D8 would = -$2.00. This is what I have come up with so far (D5): Code: -------------------- =IF(C5="",IF(C4="","", ???),C5*B5) -------------------- Any ideas? -- c_schear --------------------------------------------------------------------- --- c_schear's Profile: http://www.excelforum.com/member.php...o&userid=16494 View this thread: http://www.excelforum.com/showthread...hreadid=278720 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
finding common numbers in large lists | Excel Worksheet Functions |