SUM C IF B=Cap... Till Next B=Cap...
 

Having data below in an excel spreadsheet how can I make a unique formula in
column D that gives in lines where the text in Column B begins with "Cap" the
sum of column C till next line where B begins with "Cap" ????

Best Regards, and Thanks for Help

Correction:
A1 B2 C2 D2 E2
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End


--
Arcindo RA Lucas

=IF(LEFT(B2,3)="Cap",SUM(B3:B$65535)-SUM(D3:D$65535),"")

Tyr pasting that in cell D2 and autofill down to each row of data.


"cradino" wrote:

Having data below in an excel spreadsheet how can I make a unique formula in
column D that gives in lines where the text in Column B begins with "Cap" the
sum of column C till next line where B begins with "Cap" ????

Best Regards, and Thanks for Help

Correction:
A1 B2 C2 D2 E2
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End


--
Arcindo RA Lucas

Thanks bpeltzer
But it doesn't result
Note I only want to sum data from columnn C. Repair
Cell D2 must be 63=10+21+32 =SUM(Since C2, where B2 Begins with Cap, till C6
where B6 Begins again with Cap. Excuse me but I don't see how your formula
gets this intent.
Cell
A1 B1 C1 D1 E1
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End

--
Arcindo RA Lucas


"bpeltzer" escreveu:

=IF(LEFT(B2,3)="Cap",SUM(B3:B$65535)-SUM(D3:D$65535),"")

Tyr pasting that in cell D2 and autofill down to each row of data.


"cradino" wrote:

Having data below in an excel spreadsheet how can I make a unique formula in
column D that gives in lines where the text in Column B begins with "Cap" the
sum of column C till next line where B begins with "Cap" ????

Best Regards, and Thanks for Help

Correction:
A1 B2 C2 D2 E2
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End


--
Arcindo RA Lucas

This should work as long as you have Cap in the last row of data + 1

=IF(LEFT(B2,3)="Cap",SUM(OFFSET(C3,0,0,MATCH("Cap" ,LEFT(B3:$B$100,3),0)-1)),
"")

it is an array formula so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"cradino" wrote in message
...
Having data below in an excel spreadsheet how can I make a unique formula
in
column D that gives in lines where the text in Column B begins with "Cap"
the
sum of column C till next line where B begins with "Cap" ????

Best Regards, and Thanks for Help

Correction:
A1 B2 C2 D2 E2
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End


--
Arcindo RA Lucas

Hello Bob Phillips
Thanks for your post.
I understand your function, it is intelligent, all the tests of the enclosed
functions give accurate results - selecting and typing F9, as you know-, but
the final result always gives the error "# VALUE".
Have you tested it?
Arcindo Lucas

"Bob Phillips" escreveu:

This should work as long as you have Cap in the last row of data + 1

=IF(LEFT(B2,3)="Cap",SUM(OFFSET(C3,0,0,MATCH("Cap" ,LEFT(B3:$B$100,3),0)-1)),
"")

it is an array formula so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"cradino" wrote in message
...
Having data below in an excel spreadsheet how can I make a unique formula
in
column D that gives in lines where the text in Column B begins with "Cap"
the
sum of column C till next line where B begins with "Cap" ????

Best Regards, and Thanks for Help

Correction:
A1 B2 C2 D2 E2
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End


--
Arcindo RA Lucas

Yes I did.

Two things to note.

The formula wrapped in the mail, so the "") should be on the same line as
the first part.

Did you note my corollary

it is an array formula so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"cradino" wrote in message
...
Hello Bob Phillips
Thanks for your post.
I understand your function, it is intelligent, all the tests of the
enclosed
functions give accurate results - selecting and typing F9, as you know-,
but
the final result always gives the error "# VALUE".
Have you tested it?
Arcindo Lucas

"Bob Phillips" escreveu:

This should work as long as you have Cap in the last row of data + 1


=IF(LEFT(B2,3)="Cap",SUM(OFFSET(C3,0,0,MATCH("Cap" ,LEFT(B3:$B$100,3),0)-1)),
"")

it is an array formula so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"cradino" wrote in message
...
Having data below in an excel spreadsheet how can I make a unique
formula
in
column D that gives in lines where the text in Column B begins with
"Cap"
the
sum of column C till next line where B begins with "Cap" ????

Best Regards, and Thanks for Help

Correction:
A1 B2 C2 D2 E2
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End


--
Arcindo RA Lucas

Thanks bpeltzer
I didn't see correctly. Your function is the best. It woks fine.
--
Arcindo RA Lucas


"cradino" escreveu:

Thanks bpeltzer
But it doesn't result
Note I only want to sum data from columnn C. Repair
Cell D2 must be 63=10+21+32 =SUM(Since C2, where B2 Begins with Cap, till C6
where B6 Begins again with Cap. Excuse me but I don't see how your formula
gets this intent.
Cell
A1 B1 C1 D1 E1
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End

--
Arcindo RA Lucas


"bpeltzer" escreveu:

=IF(LEFT(B2,3)="Cap",SUM(B3:B$65535)-SUM(D3:D$65535),"")

Tyr pasting that in cell D2 and autofill down to each row of data.


"cradino" wrote:

Having data below in an excel spreadsheet how can I make a unique formula in
column D that gives in lines where the text in Column B begins with "Cap" the
sum of column C till next line where B begins with "Cap" ????

Best Regards, and Thanks for Help

Correction:
A1 B2 C2 D2 E2
A2 Cap1 63 '=SOMA(C3:C5)
A3 10
A4 21
A5 32
A6 Cap2 41 '=SOMA(C7:C8)
A7 8
A8 33
A9 Cap3 44 '=SOMA(C10:C12)
A10 33
A11 6
A12 5
A13 End


--
Arcindo RA Lucas