SUM and COunt

[Vasanth88ece]

Hi All,

Is it possible to count and sum in the range of values. Example. If ah column has numbers then it should sum it and if it is string it should count.

 

[Peter_SSs]

If the column will have numbers only or text only then try the formula shown in columns A & B below. If the column could have a mixture then perhaps the column D formula might be what you want?

25 03 03.xlsm
	A	B	C	D
1	Numbers	Text		Mixture
2	5	a		a
3		f		4
4		d		5
5	6			f
6	4	s
7	15	4		9 | 2
Numbers or Text

Cell Formulas
Range	Formula
A7:B7	A7	=IF(COUNT(A2:A6),SUM(A2:A6),COUNTA(A2:A6))
D7	D7	=SUM(D2:D6)&" | "&COUNTIF(D2:D6,"?*")

 

[Vasanth88ece]

If the column will have numbers only or text only then try the formula shown in columns A & B below. If the column could have a mixture then perhaps the column D formula might be what you want?

25 03 03.xlsm
	A	B	C	D
1	Numbers	Text		Mixture
2	5	a		a
3		f		4
4		d		5
5	6			f
6	4	s
7	15	4		9 | 2
Numbers or Text

Cell Formulas
Range	Formula
A7:B7	A7	=IF(COUNT(A2:A6),SUM(A2:A6),COUNTA(A2:A6))
D7	D7	=SUM(D2:D6)&" | "&COUNTIF(D2:D6,"?*")

Thank you !. But actually my data is like below. If the row has text it should count and the row has numbers it should sum. The row values will be in either text or number and not mix of both.

26/Thu	27/Fri	28/Sat	29/Sun	30/Mon	31/Tue	01/Wed	02/Thu	03/Fri	04/Sat	05/Sun	06/Mon	07/Tue	08/Wed	09/Thu	10/Fri	11/Sat	12/Sun	13/Mon	14/Tue	15/Wed	16/Thu	17/Fri	18/Sat	19/Sun	20/Mon	21/Tue	22/Wed	23/Thu	24/Fri	25/Sat	26/Sun	27/Mon	28/Tue	29/Wed	30/Thu	31/Fri
NA	NA	NA	NA	NA	NA	A	P	P	P	P	A	A	A	P	P	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
NA	NA	NA	NA	NA	NA	H	-	-	-	S	-	-	-	-	-	-	S	-	-	-	-	-	-	S	-	-	-	-	10	22	S	12	12	12	12	12
NA	NA	NA	NA	NA	NA	H	P	P	W/O	P	P	P	P	P	P	W/O	P	P	P	P	P	A	W/O	P	P	P	P	P	P	W/O	P	P	P	P	-	-
NA	NA	NA	NA	NA	NA	8	10	10	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
12	12	12	6	12	12	H	12	12	11	S	11	11	11	11	11	12	6	12	12	12	12	12	12	12	12	12	12	12	12	12	NA	NA	NA	NA	NA	NA

 

[Peter_SSs]

If the row ...

Hmm - you did say column in post 1

If ah column has ...

The row values will be in either text or number and not mix of both.

Yet you do have rows with both numbers and text

 

[Vasanth88ece]

Hmm - you did say column in post 1



Yet you do have rows with both numbers and text

View attachment 122900

Hmm - you did say column in post 1

My bad. Okay For the count I m going to count a specific string and that row will never have a number by any chance.

In SUM range(My specific string wont be available) anyways it is going to ignore the text and only sum the numeric values. Correct me if my thinking is wrong.

 

[Peter_SSs]

For the count I m going to count a specific string and that row will never have a number by any chance.

In SUM range(My specific string wont be available) anyways it is going to ignore the text and only sum the numeric values. Correct me if my thinking is wrong.

Could we have another (small - we don't need so many columns to demonstrate the concept) set of sample data and the expected results and explain again in relation to that sample data, including telling us what the "specific string" is in that data?

 

[Vasanth88ece]

Could we have another (small - we don't need so many columns to demonstrate the concept) set of sample data and the expected results and explain again in relation to that sample data, including telling us what the "specific string" is in that data?

john	P	A	P	P	I need to count the P present in this row
steve	11	23	45	11	I need to sum the values in this row
max	12	45	78	78	I need to sum the values in this row

I wanna do the above with sum and countif together. wherever countif is required it should perform countif similarly sum function. The above pattern is constant.

 

[Peter_SSs]

Try this then

25 03 04.xlsm
	B	C	D	E	F
2	P	A	P	P	3
3	11	23	45	11	90
4	12	45	78	78	213
Sheet4

Cell Formulas
Range	Formula
F2:F4	F2	=IF(COUNT(B2:E2),SUM(B2:E2),COUNTIF(B2:E2,"P"))

 

[Vasanth88ece]

Try this then

25 03 04.xlsm
	B	C	D	E	F
2	P	A	P	P	3
3	11	23	45	11	90
4	12	45	78	78	213
Sheet4

Cell Formulas
Range	Formula
F2:F4	F2	=IF(COUNT(B2:E2),SUM(B2:E2),COUNTIF(B2:E2,"P"))

Thank you !

 

[Peter_SSs]

You are welcome.