Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default I have a column of numbers, some positive, some negative.

I would like to count the number of entries that are 3 and are <=7.
What function would I use? Thanks for any help.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 36
Default I have a column of numbers, some positive, some negative.

On Mar 23, 8:09*am, Skip wrote:
I would like to count the number of entries that are 3 and are <=7.
What function would I use? *Thanks for any help.


In xl 2007 look in the help index for countIFS. In less than xl 2007
look in the help index for COUNTIF and AND
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default I have a column of numbers, some positive, some negative.

On Mar 23, 6:09*am, Skip wrote:
I would like to count the number of entries that are
3 and are <=7. What function would I use?


One way:

=COUNTIF(A1:A100,"<=7")-COUNTIF(A1:A100,"<=3")

More straight-forwardly:

=SUMPRODUCT((A1:A1003)*(A1:A100<=7))

=COUNTIFS(A1:A100,"3",A1:A100,"<=7")

The last one is the best. But it is available only in XL2007 and
later.

In the SUMPRODUCT, multiply (*) acts like AND. We cannot use AND in
that context.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default I have a column of numbers, some positive, some negative.

On Mar 23, 1:11*pm, joeu2004 wrote:
On Mar 23, 6:09*am, Skip wrote:

I would like to count the number of entries that are
3 and are <=7. *What function would I use?


One way:

=COUNTIF(A1:A100,"<=7")-COUNTIF(A1:A100,"<=3")

More straight-forwardly:

=SUMPRODUCT((A1:A1003)*(A1:A100<=7))

=COUNTIFS(A1:A100,"3",A1:A100,"<=7")

The last one is the best. *But it is available only in XL2007 and
later.

In the SUMPRODUCT, multiply (*) acts like AND. *We cannot use AND in
that context.


Thanks, SUBPRODUCT works great. Thanks again
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
I have a column of numbers, some negative, some positive Skip[_2_] Excel Worksheet Functions 3 November 19th 09 01:28 AM
How do I convert a column of numbers from positive to negative? BJ Excel Discussion (Misc queries) 3 March 19th 08 10:38 PM
Excel, change column of negative numbers to positive numbers? Nita New Users to Excel 3 November 27th 07 05:54 AM
Convert a column of numbers from positive to negative in Excel JRoseen Excel Discussion (Misc queries) 4 July 7th 06 07:18 PM
How do I change a column of 500 numbers from positive to negative JKB Excel Discussion (Misc queries) 2 September 28th 05 04:16 PM


All times are GMT +1. The time now is 03:30 PM.

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"