lookup/sum formula
 

I am working with a spreadsheet which has numbers and text in fields over a
large area:

for example, cells in a row would be: 435, 250, TEXT1, 400, 300, text2,
500....etc.
what I am looking to accomplish is to set a formula to say:

for each TEXT1 in a row, there should be a value of 1 added to a sum formula.

so if there are 6 TEXT1 cells in the row, the formula would result in a
value of 6.

Are there any formulas that can accomplish this in excel?

Any help is great.

lookup/sum formula
 

Try the below formula for range A1:A10. Please note that this is an array
formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula}"

=SUM(A1:A10)+SUM(IF(ISTEXT(A1:A10),1,0))

If this post helps click Yes
---------------
Jacob Skaria


"dgabriel" wrote:

I am working with a spreadsheet which has numbers and text in fields over a
large area:

for example, cells in a row would be: 435, 250, TEXT1, 400, 300, text2,
500....etc.
what I am looking to accomplish is to set a formula to say:

for each TEXT1 in a row, there should be a value of 1 added to a sum formula.

so if there are 6 TEXT1 cells in the row, the formula would result in a
value of 6.

Are there any formulas that can accomplish this in excel?

Any help is great.

lookup/sum formula
 

Hi,

Maybe this

=COUNTIF(A2:O2,"Text1")

Mike

"dgabriel" wrote:

I am working with a spreadsheet which has numbers and text in fields over a
large area:

for example, cells in a row would be: 435, 250, TEXT1, 400, 300, text2,
500....etc.
what I am looking to accomplish is to set a formula to say:

for each TEXT1 in a row, there should be a value of 1 added to a sum formula.

so if there are 6 TEXT1 cells in the row, the formula would result in a
value of 6.

Are there any formulas that can accomplish this in excel?

Any help is great.

lookup/sum formula
 

Oops. The below formula will count any TEXT entries... For counting a
specific text such as "TEXT1" use COUNTIF(A1:A10,"Text1")

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below formula for range A1:A10. Please note that this is an array
formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula}"

=SUM(A1:A10)+SUM(IF(ISTEXT(A1:A10),1,0))

If this post helps click Yes
---------------
Jacob Skaria


"dgabriel" wrote:

I am working with a spreadsheet which has numbers and text in fields over a
large area:

for example, cells in a row would be: 435, 250, TEXT1, 400, 300, text2,
500....etc.
what I am looking to accomplish is to set a formula to say:

for each TEXT1 in a row, there should be a value of 1 added to a sum formula.

so if there are 6 TEXT1 cells in the row, the formula would result in a
value of 6.

Are there any formulas that can accomplish this in excel?

Any help is great.

lookup/sum formula
 

jacob and Mike.

Countif worked perfectly. Knew there had to be a formula for that..

Thanks!!

"Mike H" wrote:

Hi,

Maybe this

=COUNTIF(A2:O2,"Text1")

Mike

"dgabriel" wrote:

I am working with a spreadsheet which has numbers and text in fields over a
large area:

for example, cells in a row would be: 435, 250, TEXT1, 400, 300, text2,
500....etc.
what I am looking to accomplish is to set a formula to say:

for each TEXT1 in a row, there should be a value of 1 added to a sum formula.

so if there are 6 TEXT1 cells in the row, the formula would result in a
value of 6.

Are there any formulas that can accomplish this in excel?

Any help is great.