How to SUM & COUNT Between Two Text Strings?

[mzi]

I want to SUM and COUNT Values between two text strings

Suppose in Row1, I have values like this
1,0,2,OUT,4,.,.,1,6,OUT,1,3

Now I want to SUM & COUNT Values Before, Between and After the Text "OUT"...
So,
For the SUM Function, the result should be
3 in the Cell A2, 11 in the Cell B2 and 4 in the Cell C2...
For the COUNT Function, the result should be
3 in the Cell A3, 5 in the Cell B3 and 2 in the Cell C3...

I'll be very thankful if you help me...

 

[Peter_SSs]

It isn't clear ..

- if there can be more (or less) than 2 OUT values in the row, but there will be at least one OUT

- if there are lots of rows like this to deal with

- if there is a maximum number of columns that the data can occupy

My suggestion below assumes ..

- there can be more/less than 2 OUT values in the row

- the are not too many of these rows to deal with as I have used the volatile OFFSET function which may adversely affect your sheet performance if too many of these formulas are used on your sheet.

- data does not go beyond column Z

- that you are using Excel 2007 or later


Formulas in column A are stand-alone
Formulas in column B are copied across as far as you might need
Row 4 is used as a helper row.

These formulas may need further tweaking depending on just what might occur in your sheet.

If this doesn't meet your needs, please give more examples of sample data and expected results.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	0	2	OUT	4	.	.	1	6	OUT	1	3
2	3	11	4
3	3	5	2
4	4	10	26
5
mzi

 

[Akashwani]

Hi,

This got me curious, so I searched around and came up with the following, none of which are my solutions, except for the Substitute formula!!

Excel Workbook
	A	B	C	D	E	F	G	H
1		Left	Middle	Right
2		102	416	13		102OUT416OUT13		1,0,2,OUT,4,.,.,1,6,OUT,1,3
3	Sum	3	11	4
4	Count	3	3	2
Sheet6

I hope that helps.

Good luck.

Ak

 

[Marcol]

Another suggestion.

This should handle any length of row after Column D, provided "OUT" appers twice in the row.
(D1:O1 is only for a check, it isn't required)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	4	10	12	1	2	3	4	5	6	7	8	9	10	11	12
2	3	11	4	1	0	2	OUT	4	0	0	1	6	OUT	1	3
3	3	5	2
Sheet1

 

[mzi]

It isn't clear ..

- if there can be more (or less) than 2 OUT values in the row, but there will be at least one OUT

- if there are lots of rows like this to deal with

- if there is a maximum number of columns that the data can occupy

My suggestion below assumes ..

- there can be more/less than 2 OUT values in the row

- the are not too many of these rows to deal with as I have used the volatile OFFSET function which may adversely affect your sheet performance if too many of these formulas are used on your sheet.

- data does not go beyond column Z

- that you are using Excel 2007 or later


Formulas in column A are stand-alone
Formulas in column B are copied across as far as you might need
Row 4 is used as a helper row.

These formulas may need further tweaking depending on just what might occur in your sheet.

If this doesn't meet your needs, please give more examples of sample data and expected results.

mzi

A

B

C

D

E

F

G

H

I

J

K

L

M

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"><col style="width:41px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]OUT[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"].[/TD]
[TD="align: center"].[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]OUT[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]26[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 =SUM(OFFSET(A1,0,0,1,A4)) B2 =IF(B4="","",SUM(OFFSET($A1,0,A4,1,B4-A4))) A3 =COUNTA(OFFSET(A1,0,0,1,A4))-1 B3 =IF(B4="","",COUNTA(OFFSET($A1,0,A4,1,B4-A4))-1+(C4="")) A4 =MATCH("OUT",A1:Z1,0) B4 =IF(COLUMNS($B4:B4)>COUNTIF($A1:$Z1,"OUT"),"",IFERROR(MATCH("OUT",INDEX($A1:$Z1,A4+1):Z1,0)+A4,COLUMNS($A1:$Z1))) <tbody> </tbody>

<tbody>
</tbody>

Thanx a Lot Sir, it worked perfectly for me, the results with these formulas are exactly what i want thank you so much....

 

[mzi]

Thank you Mr. Akashwani and Mr. Marcol, your formulas helped me, thanx for you suggestions...

 

[Peter_SSs]

Thanx a Lot Sir, it worked perfectly for me, the results with these formulas are exactly what i want thank you so much....

Glad it worked for you.

Another suggestion.

This should handle any length of row after Column D, provided "OUT" appers twice in the row.

A couple of comments ..

- It can only be assured of handling any length row if the user is using Excel 2003 or older (though I acknowledge it may be unlikely that more columns are used anyway).

- It would fail if it is possible that OUT is the first or last item in the row

If we assume okay to use columns D:IV, exactly 2 OUT values and all other data is numerical (I interpreted the original post as having "." values in the row as well), then A2:C3 below would be an alternative approach with more straight-forward formulas, also avoiding OFFSET, and handling OUT being first/last.

If other text values are allowed in the data, then the formulas in A4:C4 could be used instead of those in A3:C3.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	4	10
2	3	11	4	1	0	2	OUT	4	0	0	1	6	OUT	1	3
3	3	5	2
4	3	5	2
Sum and Count