Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jurry
 
Posts: n/a
Default Countif & ranges consisting of multiple areas


Another reason for the use of a dynamic created range is that in this
way cells with "N/A" are already discarded. So they will not mess up
the sumproduct calculation (or is there a method to exclude the "N/A"
cells from that calculation, other than making an additional row on the
sheet?).

Jurry.


--
Jurry
------------------------------------------------------------------------
Jurry's Profile: http://www.excelforum.com/member.php...fo&userid=8664
View this thread: http://www.excelforum.com/showthread...hreadid=278230

  #2   Report Post  
Daniel.M
 
Posts: n/a
Default

Hi,

Another reason for the use of a dynamic created range is that in this
way cells with "N/A" are already discarded. So they will not mess up
the sumproduct calculation (or is there a method to exclude the "N/A"
cells from that calculation, other than making an additional row on the
sheet?).


No need to create/define multiple AREAS.
The following will work as it's bypassing any errors (#N/A) you might have in
the range.

testrange=A1:A6
B1=COUNTIF (testrange,"5")

Otherwise, if you DO have multiple conditions, the following ARRAY formula:

=SUM(IF(ISNUMBER(testrange),(testrange5)*(testran ge<40)*testrange))

Regards,

Daniel M.


  #3   Report Post  
Daniel.M
 
Posts: n/a
Default

Hi,

Otherwise, if you DO have multiple conditions, the following ARRAY formula:

=SUM(IF(ISNUMBER(testrange),(testrange5)*(testran ge<40)*testrange))


The previous formula SUM-up the numbers.
If you only want to count them, this ARRAY (Ctrl-Shift-Enter) formula:

=SUM(IF(ISNUMBER(testrange),(testrange5)*(testran ge<40)))


Regards,

Daniel M.


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 Link named ranges from multiple Workbooks into a single Wo. PMAP_HELP Excel Discussion (Misc queries) 1 December 14th 04 06:51 PM
"countif" from multiple worksheets within workbook Excel Discussion (Misc queries) 2 December 10th 04 07:59 PM
compare unique identifiers in multiple ranges bawilli_91125 Charts and Charting in Excel 1 November 30th 04 07:34 PM
Countif & ranges consisting of multiple areas Jurry Excel Worksheet Functions 1 November 15th 04 01:25 PM
Countif & ranges consisting of multiple areas Jurry Excel Worksheet Functions 1 November 15th 04 10:03 AM


All times are GMT +1. The time now is 07:45 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"