Count number of cells and total in one column, based on another column suffix

[Pierre]

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

 

[Guest]

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

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Pierre]

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







- Show quoted text -

Both takes worked like a charm. Thank you both, Elkar and Dave.

 

[Pierre]

=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 here:http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html

This sumproduct stuff is indespensible. Thx. again.
Pierre

 

[Dave Peterson]

=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.