Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula | Excel Discussion (Misc queries) | |||
Lookup and Use a Formula | Excel Discussion (Misc queries) | |||
LOOKUP Formula | Excel Worksheet Functions | |||
Lookup formula | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |