Remember Me? October 30th 07, 07:17 PM posted to microsoft.public.excel.worksheet.functions
 Pierre external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 193 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 October 30th 07, 07:38 PM posted to microsoft.public.excel.worksheet.functions
 Elkar external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 964 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 10<""))

Sum:
=SUMPRODUCT(--(RIGHT(\$C\$1:\$C\$10,2)="=T"),D1 10)

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 October 30th 07, 07:40 PM posted to microsoft.public.excel.worksheet.functions
 Dave Peterson external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218 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 October 30th 07, 07:57 PM posted to microsoft.public.excel.worksheet.functions
 Pierre external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 193 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 10<""))

Sum:
=SUMPRODUCT(--(RIGHT(\$C\$1:\$C\$10,2)="=T"),D1 10)

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. October 30th 07, 08:11 PM posted to microsoft.public.excel.worksheet.functions
 Pierre external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 193 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 October 31st 07, 12:28 AM posted to microsoft.public.excel.worksheet.functions
 Dave Peterson external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Mike K[_2_] Excel Discussion (Misc queries) 5 May 9th 07 08:41 PM Beach Lover Excel Discussion (Misc queries) 9 February 19th 07 03:39 PM Kurt Excel Worksheet Functions 7 January 10th 07 09:29 PM Mike Excel Worksheet Functions 1 October 13th 06 07:59 PM [email protected] New Users to Excel 1 May 5th 05 08:11 PM

All times are GMT +1. The time now is 07:35 PM. Copyright ©2004-2019 ExcelBanter.