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

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

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