If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
please try this
First of all, i hope this is easy.
Is it possible *?* to have a subtotal() formula in a column that can count the cells like; Formula 1) count the visible cells in a column which start with a text "C". Formula 2) count the visible cells in a column which start with a text "C" & next number(s) is 2 or more. (e.g. if cell contains text like "C1df", subtotal will not count, if the cell contains text like "C2dfg" or "C10jky" or "C6KL", then subtotal will consider to count this.). Please try to help me on the above subject. -- regards, |
#2
|
|||
|
|||
please try this
This won't be possible with Subtotal. There is no "SubtotalIf" function in
Excel. The best solution is likely Sumproduct. In this function, you can check for a starting "C", as well as the filter conditions that create the visitble cells. Your second request also has difficulties because you want to consider 10 greater than 2, which is true if it's a number, but not when it's text. The easiest solution would be to create a helper column with the number in it. Then it's easy to compare. Post back if you need more help. Regards, Fred. "driller" wrote in message ... First of all, i hope this is easy. Is it possible *?* to have a subtotal() formula in a column that can count the cells like; Formula 1) count the visible cells in a column which start with a text "C". Formula 2) count the visible cells in a column which start with a text "C" & next number(s) is 2 or more. (e.g. if cell contains text like "C1df", subtotal will not count, if the cell contains text like "C2dfg" or "C10jky" or "C6KL", then subtotal will consider to count this.). Please try to help me on the above subject. -- regards, |
#3
|
|||
|
|||
please try this
There is a SUBTOTAL function and it can be used together with SUMPRODUCT to
get a count, sum etc with criteria. http://tinyurl.com/5hwu7v -- Regards, Peo Sjoblom "Fred Smith" wrote in message ... This won't be possible with Subtotal. There is no "SubtotalIf" function in Excel. The best solution is likely Sumproduct. In this function, you can check for a starting "C", as well as the filter conditions that create the visitble cells. Your second request also has difficulties because you want to consider 10 greater than 2, which is true if it's a number, but not when it's text. The easiest solution would be to create a helper column with the number in it. Then it's easy to compare. Post back if you need more help. Regards, Fred. "driller" wrote in message ... First of all, i hope this is easy. Is it possible *?* to have a subtotal() formula in a column that can count the cells like; Formula 1) count the visible cells in a column which start with a text "C". Formula 2) count the visible cells in a column which start with a text "C" & next number(s) is 2 or more. (e.g. if cell contains text like "C1df", subtotal will not count, if the cell contains text like "C2dfg" or "C10jky" or "C6KL", then subtotal will consider to count this.). Please try to help me on the above subject. -- regards, |
#4
|
|||
|
|||
please try this
Impressive.
Picking out the number following the "C" is still going to be an issue, depending on how many digits need to be supported. Fred. "Peo Sjoblom" wrote in message ... There is a SUBTOTAL function and it can be used together with SUMPRODUCT to get a count, sum etc with criteria. http://tinyurl.com/5hwu7v -- Regards, Peo Sjoblom "Fred Smith" wrote in message ... This won't be possible with Subtotal. There is no "SubtotalIf" function in Excel. The best solution is likely Sumproduct. In this function, you can check for a starting "C", as well as the filter conditions that create the visitble cells. Your second request also has difficulties because you want to consider 10 greater than 2, which is true if it's a number, but not when it's text. The easiest solution would be to create a helper column with the number in it. Then it's easy to compare. Post back if you need more help. Regards, Fred. "driller" wrote in message ... First of all, i hope this is easy. Is it possible *?* to have a subtotal() formula in a column that can count the cells like; Formula 1) count the visible cells in a column which start with a text "C". Formula 2) count the visible cells in a column which start with a text "C" & next number(s) is 2 or more. (e.g. if cell contains text like "C1df", subtotal will not count, if the cell contains text like "C2dfg" or "C10jky" or "C6KL", then subtotal will consider to count this.). Please try to help me on the above subject. -- regards, |
#5
|
|||
|
|||
please try this
the numbers after C had 2 digit max (e.g C1*- up to C99*).
thanks for trying to help. -- regards, "Fred Smith" wrote: Impressive. Picking out the number following the "C" is still going to be an issue, depending on how many digits need to be supported. Fred. "Peo Sjoblom" wrote in message ... There is a SUBTOTAL function and it can be used together with SUMPRODUCT to get a count, sum etc with criteria. http://tinyurl.com/5hwu7v -- Regards, Peo Sjoblom "Fred Smith" wrote in message ... This won't be possible with Subtotal. There is no "SubtotalIf" function in Excel. The best solution is likely Sumproduct. In this function, you can check for a starting "C", as well as the filter conditions that create the visitble cells. Your second request also has difficulties because you want to consider 10 greater than 2, which is true if it's a number, but not when it's text. The easiest solution would be to create a helper column with the number in it. Then it's easy to compare. Post back if you need more help. Regards, Fred. "driller" wrote in message ... First of all, i hope this is easy. Is it possible *?* to have a subtotal() formula in a column that can count the cells like; Formula 1) count the visible cells in a column which start with a text "C". Formula 2) count the visible cells in a column which start with a text "C" & next number(s) is 2 or more. (e.g. if cell contains text like "C1df", subtotal will not count, if the cell contains text like "C2dfg" or "C10jky" or "C6KL", then subtotal will consider to count this.). Please try to help me on the above subject. -- regards, |
#6
|
|||
|
|||
please try this
Well, at least the first part is easy. You use Peo's solution to check for
filtered records, and add a check for Left(range,1)="C" For your second problem, I would create a helper column that extracts the number which follows the C. Then you simple check whether it's greater than 1. If you can't, or don't want to, create a helper column, I suggest you create a new post specifically asking how you create a Sumproduct entry checking for greater than 1 in your sample data. I'm sure someone smarter than I will be able to help you. Regards, Fred. "driller" wrote in message ... the numbers after C had 2 digit max (e.g C1*- up to C99*). thanks for trying to help. -- regards, "Fred Smith" wrote: Impressive. Picking out the number following the "C" is still going to be an issue, depending on how many digits need to be supported. Fred. "Peo Sjoblom" wrote in message ... There is a SUBTOTAL function and it can be used together with SUMPRODUCT to get a count, sum etc with criteria. http://tinyurl.com/5hwu7v -- Regards, Peo Sjoblom "Fred Smith" wrote in message ... This won't be possible with Subtotal. There is no "SubtotalIf" function in Excel. The best solution is likely Sumproduct. In this function, you can check for a starting "C", as well as the filter conditions that create the visitble cells. Your second request also has difficulties because you want to consider 10 greater than 2, which is true if it's a number, but not when it's text. The easiest solution would be to create a helper column with the number in it. Then it's easy to compare. Post back if you need more help. Regards, Fred. "driller" wrote in message ... First of all, i hope this is easy. Is it possible *?* to have a subtotal() formula in a column that can count the cells like; Formula 1) count the visible cells in a column which start with a text "C". Formula 2) count the visible cells in a column which start with a text "C" & next number(s) is 2 or more. (e.g. if cell contains text like "C1df", subtotal will not count, if the cell contains text like "C2dfg" or "C10jky" or "C6KL", then subtotal will consider to count this.). Please try to help me on the above subject. -- regards, |
#7
|
|||
|
|||
please try this
Fred.
I actually think it is better to use helper columns, it is definitely better from a design point of view even if it is impressive that some can come up with these formulas. I believe the originator is Laurent Longre, the same person who wrote Morefunc which I personally think is a great add-in. -- Regards, Peo Sjoblom "Fred Smith" wrote in message ... Well, at least the first part is easy. You use Peo's solution to check for filtered records, and add a check for Left(range,1)="C" For your second problem, I would create a helper column that extracts the number which follows the C. Then you simple check whether it's greater than 1. If you can't, or don't want to, create a helper column, I suggest you create a new post specifically asking how you create a Sumproduct entry checking for greater than 1 in your sample data. I'm sure someone smarter than I will be able to help you. Regards, Fred. "driller" wrote in message ... the numbers after C had 2 digit max (e.g C1*- up to C99*). thanks for trying to help. -- regards, "Fred Smith" wrote: Impressive. Picking out the number following the "C" is still going to be an issue, depending on how many digits need to be supported. Fred. "Peo Sjoblom" wrote in message ... There is a SUBTOTAL function and it can be used together with SUMPRODUCT to get a count, sum etc with criteria. http://tinyurl.com/5hwu7v -- Regards, Peo Sjoblom "Fred Smith" wrote in message ... This won't be possible with Subtotal. There is no "SubtotalIf" function in Excel. The best solution is likely Sumproduct. In this function, you can check for a starting "C", as well as the filter conditions that create the visitble cells. Your second request also has difficulties because you want to consider 10 greater than 2, which is true if it's a number, but not when it's text. The easiest solution would be to create a helper column with the number in it. Then it's easy to compare. Post back if you need more help. Regards, Fred. "driller" wrote in message ... First of all, i hope this is easy. Is it possible *?* to have a subtotal() formula in a column that can count the cells like; Formula 1) count the visible cells in a column which start with a text "C". Formula 2) count the visible cells in a column which start with a text "C" & next number(s) is 2 or more. (e.g. if cell contains text like "C1df", subtotal will not count, if the cell contains text like "C2dfg" or "C10jky" or "C6KL", then subtotal will consider to count this.). Please try to help me on the above subject. -- regards, |
Thread Tools | |
Display Modes | |
|
|