Counting once more

[JRD]

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

 

[Peo Sjoblom]

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

 

[T. Valko]

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)