Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting once more
How do I sum a column of numbers but only if the cell in the adjacent column
contains a date within a certain range, also ignoring any #N/A errors e.g. A B 1 1st October 2007 1 2 31st October 2007 2 3 22nd October 2007 #N/A 4 1st January 2007 3 5 22nd January 2007 2 So if wanted to sum the numbers in column B, but only including the numbers where the corresponding cell in column A is between 1st October and 31st October (note column A is in date format), and ignoring the #N/A errors. The answer here would be 3. Thanks John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting once more
If those are real numerical dates you can use
=SUM(IF(ISNA(B1:B20),0,(A1:A20=DATE(2007,10,1))*( A1:A20<=DATE(2007,10,31))*(B1:B20))) entered with ctrl + shift & enter if not you should make them real numerical excel dates -- Regards, Peo Sjoblom "JRD" wrote in message ... How do I sum a column of numbers but only if the cell in the adjacent column contains a date within a certain range, also ignoring any #N/A errors e.g. A B 1 1st October 2007 1 2 31st October 2007 2 3 22nd October 2007 #N/A 4 1st January 2007 3 5 22nd January 2007 2 So if wanted to sum the numbers in column B, but only including the numbers where the corresponding cell in column A is between 1st October and 31st October (note column A is in date format), and ignoring the #N/A errors. The answer here would be 3. Thanks John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting once more
Are your dates true Excel dates? They don't look like they are.
Assuming the dates are true Excel dates, try this array formula** : A10 = 10/1/2007 B10 = 10/31/2007 =SUM(IF((A1:A5=A10)*(A1:A5<=B10)*(ISNUMBER(B1:B5) ),B1:B5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "JRD" wrote in message ... How do I sum a column of numbers but only if the cell in the adjacent column contains a date within a certain range, also ignoring any #N/A errors e.g. A B 1 1st October 2007 1 2 31st October 2007 2 3 22nd October 2007 #N/A 4 1st January 2007 3 5 22nd January 2007 2 So if wanted to sum the numbers in column B, but only including the numbers where the corresponding cell in column A is between 1st October and 31st October (note column A is in date format), and ignoring the #N/A errors. The answer here would be 3. Thanks John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting function but not double counting duplicates | Excel Worksheet Functions | |||
counting | Excel Discussion (Misc queries) | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Counting! | Excel Discussion (Misc queries) |