Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
"countif" from multiple worksheets within workbook | Excel Discussion (Misc queries) | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
Countif & ranges consisting of multiple areas | Excel Worksheet Functions | |||
Countif & ranges consisting of multiple areas | Excel Worksheet Functions |