Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count number of cells and total in one column, based on another column suffix
Have in column C values, some of which end in =T.
I have data in columns D through H some values(numbers). I'd like to do two things: Obtain the number of cells that are in each column that have a corresponding value that ends with a =T in column C. Also, obtain the total of the values of the cells in each column, also of which have a corresponding value that ends =T in column C Tried messing with sumproduct, but haven't hit on the right formula. TIA for thoughts. Pierre |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count number of cells and total in one column, based on another co
See if these work for you:
Count: =SUMPRODUCT(--(RIGHT($C$1:$C$10,2)="=T"),--(D1:D10<"")) Sum: =SUMPRODUCT(--(RIGHT($C$1:$C$10,2)="=T"),D1:D10) HTH, Elkar "Pierre" wrote: Have in column C values, some of which end in =T. I have data in columns D through H some values(numbers). I'd like to do two things: Obtain the number of cells that are in each column that have a corresponding value that ends with a =T in column C. Also, obtain the total of the values of the cells in each column, also of which have a corresponding value that ends =T in column C Tried messing with sumproduct, but haven't hit on the right formula. TIA for thoughts. Pierre |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count number of cells and total in one column, based on anothercolumn suffix
=countif(c:c,"*=t")
I'm confused by the second question. Maybe =sumif(c:c,"*=t",d:d) or =countif(c:c,"*=t",d:d) or =sumproduct(--(right(c1:c100,2)="=t"),d1:d100) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Pierre wrote: Have in column C values, some of which end in =T. I have data in columns D through H some values(numbers). I'd like to do two things: Obtain the number of cells that are in each column that have a corresponding value that ends with a =T in column C. Also, obtain the total of the values of the cells in each column, also of which have a corresponding value that ends =T in column C Tried messing with sumproduct, but haven't hit on the right formula. TIA for thoughts. Pierre -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count number of cells and total in one column, based on another co
On Oct 30, 2:38 pm, Elkar wrote:
See if these work for you: Count: =SUMPRODUCT(--(RIGHT($C$1:$C$10,2)="=T"),--(D1:D10<"")) Sum: =SUMPRODUCT(--(RIGHT($C$1:$C$10,2)="=T"),D1:D10) HTH, Elkar "Pierre" wrote: Have in column C values, some of which end in =T. I have data in columns D through H some values(numbers). I'd like to do two things: Obtain the number of cells that are in each column that have a corresponding value that ends with a =T in column C. Also, obtain the total of the values of the cells in each column, also of which have a corresponding value that ends =T in column C Tried messing with sumproduct, but haven't hit on the right formula. TIA for thoughts. Pierre- Hide quoted text - - Show quoted text - Both takes worked like a charm. Thank you both, Elkar and Dave. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count number of cells and total in one column, based on another column suffix
On Oct 30, 2:40 pm, Dave Peterson wrote:
=countif(c:c,"*=t") I'm confused by the second question. Maybe =sumif(c:c,"*=t",d:d) or =countif(c:c,"*=t",d:d) or =sumproduct(--(right(c1:c100,2)="=t"),d1:d100) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail hehttp://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html Pierre wrote: Have in column C values, some of which end in =T. I have data in columns D through H some values(numbers). I'd like to do two things: Obtain the number of cells that are in each column that have a corresponding value that ends with a =T in column C. Also, obtain the total of the values of the cells in each column, also of which have a corresponding value that ends =T in column C Tried messing with sumproduct, but haven't hit on the right formula. TIA for thoughts. Pierre -- Dave Peterson This sumproduct stuff is indespensible. Thx. again. Pierre |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count number of cells and total in one column, based on anothercolumn suffix
=sumproduct() is very nice.
But if =countif() or =sumif() work, then you'll probably find them better to use. I think you'll find that they have less of a footprint when the workbook recalcs. Pierre wrote: On Oct 30, 2:40 pm, Dave Peterson wrote: =countif(c:c,"*=t") I'm confused by the second question. Maybe =sumif(c:c,"*=t",d:d) or =countif(c:c,"*=t",d:d) or =sumproduct(--(right(c1:c100,2)="=t"),d1:d100) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail hehttp://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html Pierre wrote: Have in column C values, some of which end in =T. I have data in columns D through H some values(numbers). I'd like to do two things: Obtain the number of cells that are in each column that have a corresponding value that ends with a =T in column C. Also, obtain the total of the values of the cells in each column, also of which have a corresponding value that ends =T in column C Tried messing with sumproduct, but haven't hit on the right formula. TIA for thoughts. Pierre -- Dave Peterson This sumproduct stuff is indespensible. Thx. again. Pierre -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to count number of highlighted cells in column | Excel Discussion (Misc queries) | |||
Count no. of nonblank cells in one column based on criteria of ano | Excel Discussion (Misc queries) | |||
Count entries in one column based on values in another column | Excel Worksheet Functions | |||
Can you count the number of cells that are highlighted in a column | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel |