View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default 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