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

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

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

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.

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

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