Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to count number of highlighted cells in column Mike K[_2_] Excel Discussion (Misc queries) 5 May 9th 07 08:41 PM
Count no. of nonblank cells in one column based on criteria of ano Beach Lover Excel Discussion (Misc queries) 9 February 19th 07 04:39 PM
Count entries in one column based on values in another column Kurt Excel Worksheet Functions 7 January 10th 07 10:29 PM
Can you count the number of cells that are highlighted in a column Mike Excel Worksheet Functions 1 October 13th 06 07:59 PM
Count cells based on date range in another column [email protected] New Users to Excel 1 May 5th 05 08:11 PM


All times are GMT +1. The time now is 12:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"