Home 
Search 
Today's Posts 
#1




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




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"),(D110<"")) Sum: =SUMPRODUCT((RIGHT($C$1:$C$10,2)="=T"),D110) 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




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 matchbut 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




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"),(D110<"")) Sum: =SUMPRODUCT((RIGHT($C$1:$C$10,2)="=T"),D110) 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




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 matchbut 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




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 matchbut 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 