Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif then multiply
A B C
1 S6 B2 I know how to use the countif function as if to count all values that equal "S*" (value beginning with "S" only regardless of the following number) in a row. Then tally, per row, the number of times "S*" appeared in the range. The formula for "C1" would be: =countif(A1:B2,"S*") which right now would return the value of 1. Now what I want to do is in addition to this formula, I want "C1" to also calculate the countif portion and then multiply the countif returned value by the second number in the stated cell. i.e. A1 = S6, so "C1" has a value of "1", now mulitply the "1" by the second digit in "A1" (which is 6). How would I go about this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif then multiply
Unless countif will always be 1, or if more than 1, then all cells beginning
with S will also have 6 as a second digit, your request doesn't make much sense. but for your specific case that works =countif(A1:B1,"S*")*Mid(A1,2,1) -- Regards, Tom Ogilvy "Gee-off" wrote in message ... A B C 1 S6 B2 I know how to use the countif function as if to count all values that equal "S*" (value beginning with "S" only regardless of the following number) in a row. Then tally, per row, the number of times "S*" appeared in the range. The formula for "C1" would be: =countif(A1:B2,"S*") which right now would return the value of 1. Now what I want to do is in addition to this formula, I want "C1" to also calculate the countif portion and then multiply the countif returned value by the second number in the stated cell. i.e. A1 = S6, so "C1" has a value of "1", now mulitply the "1" by the second digit in "A1" (which is 6). How would I go about this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif then multiply
A B C
1 S6 S4 The "S" indicated in A1 & B1 represents an group idenitifer for my Tool Accountability system. The number following the group identifier is the actual number of items found in that group. I guess what I really want to do is take the values in A1 & B1 and add whatever number is entered after the group identifier (regardless of how big the number is). In other words, I want C1 to return a value of 10, with no S in in that returned value. Does this make more sense? "Tom Ogilvy" wrote: Unless countif will always be 1, or if more than 1, then all cells beginning with S will also have 6 as a second digit, your request doesn't make much sense. but for your specific case that works =countif(A1:B1,"S*")*Mid(A1,2,1) -- Regards, Tom Ogilvy "Gee-off" wrote in message ... A B C 1 S6 B2 I know how to use the countif function as if to count all values that equal "S*" (value beginning with "S" only regardless of the following number) in a row. Then tally, per row, the number of times "S*" appeared in the range. The formula for "C1" would be: =countif(A1:B2,"S*") which right now would return the value of 1. Now what I want to do is in addition to this formula, I want "C1" to also calculate the countif portion and then multiply the countif returned value by the second number in the stated cell. i.e. A1 = S6, so "C1" has a value of "1", now mulitply the "1" by the second digit in "A1" (which is 6). How would I go about this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif then multiply
see response in worksheet.functions
-- HTH RP (remove nothere from the email address if mailing direct) "Gee-off" wrote in message ... A B C 1 S6 S4 The "S" indicated in A1 & B1 represents an group idenitifer for my Tool Accountability system. The number following the group identifier is the actual number of items found in that group. I guess what I really want to do is take the values in A1 & B1 and add whatever number is entered after the group identifier (regardless of how big the number is). In other words, I want C1 to return a value of 10, with no S in in that returned value. Does this make more sense? "Tom Ogilvy" wrote: Unless countif will always be 1, or if more than 1, then all cells beginning with S will also have 6 as a second digit, your request doesn't make much sense. but for your specific case that works =countif(A1:B1,"S*")*Mid(A1,2,1) -- Regards, Tom Ogilvy "Gee-off" wrote in message ... A B C 1 S6 B2 I know how to use the countif function as if to count all values that equal "S*" (value beginning with "S" only regardless of the following number) in a row. Then tally, per row, the number of times "S*" appeared in the range. The formula for "C1" would be: =countif(A1:B2,"S*") which right now would return the value of 1. Now what I want to do is in addition to this formula, I want "C1" to also calculate the countif portion and then multiply the countif returned value by the second number in the stated cell. i.e. A1 = S6, so "C1" has a value of "1", now mulitply the "1" by the second digit in "A1" (which is 6). How would I go about this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif then multiply
=Mid(A1,2)+Mid(B1,2)
-- Regards, Tom Ogilvy "Gee-off" wrote in message ... A B C 1 S6 S4 The "S" indicated in A1 & B1 represents an group idenitifer for my Tool Accountability system. The number following the group identifier is the actual number of items found in that group. I guess what I really want to do is take the values in A1 & B1 and add whatever number is entered after the group identifier (regardless of how big the number is). In other words, I want C1 to return a value of 10, with no S in in that returned value. Does this make more sense? "Tom Ogilvy" wrote: Unless countif will always be 1, or if more than 1, then all cells beginning with S will also have 6 as a second digit, your request doesn't make much sense. but for your specific case that works =countif(A1:B1,"S*")*Mid(A1,2,1) -- Regards, Tom Ogilvy "Gee-off" wrote in message ... A B C 1 S6 B2 I know how to use the countif function as if to count all values that equal "S*" (value beginning with "S" only regardless of the following number) in a row. Then tally, per row, the number of times "S*" appeared in the range. The formula for "C1" would be: =countif(A1:B2,"S*") which right now would return the value of 1. Now what I want to do is in addition to this formula, I want "C1" to also calculate the countif portion and then multiply the countif returned value by the second number in the stated cell. i.e. A1 = S6, so "C1" has a value of "1", now mulitply the "1" by the second digit in "A1" (which is 6). How would I go about this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiply CountIf in one formula | Excel Worksheet Functions | |||
look up and multiply | Excel Discussion (Misc queries) | |||
multiply, then add | Excel Discussion (Misc queries) | |||
add value of 4 cells, multiply by 3 subtract 72 multiply by 80% | Excel Discussion (Misc queries) | |||
Countif, then multiply?? | Excel Worksheet Functions |