Searching on Dates

[nmtexman]

I maintain an Excel spreadsheet that contains inventory information suc
as Manufacturer, Model, Cost, Qty, Total Cost, Date Acquired. The
column contains the dates in the mm/dd/yy format and the G colum
contains the quantity of each inventory item.

In order to break this information down by date range, I am using th
following formula:

=SUMIF('Car Inventory'!K2:K431,"<1/1/00",'Car Inventory'!G2:G431),
which works fine for selecting all items prior to Jan 1st, 2000.

However, when I need a the information for the year 2000, I do not wan
to include anything prior to 1/1/00 so I am trying to use the followin
formulat:

=SUMIF('Car Inventory'!$K$2:K431,AND(">=01/01/00","<=12/31/00"),'Ca
Inventory'!$G$2:G431)

The one in red does not work. Any suggestions

 

[Franz Verga]

Nel post *nmtexman* ha scritto:

I maintain an Excel spreadsheet that contains inventory information
such as Manufacturer, Model, Cost, Qty, Total Cost, Date Acquired.
The K column contains the dates in the mm/dd/yy format and the G
column contains the quantity of each inventory item.

In order to break this information down by date range, I am using the
following formula:

=SUMIF('Car Inventory'!K2:K431,"<1/1/00",'Car Inventory'!G2:G431),
which works fine for selecting all items prior to Jan 1st, 2000.

However, when I need a the information for the year 2000, I do not
want to include anything prior to 1/1/00 so I am trying to use the
following formulat:

=SUMIF('Car Inventory'!$K$2:K431,AND(">=01/01/00","<=12/31/00"),'Car
Inventory'!$G$2:G431)

Until Excel 2003 you can use just one condition in SUMIF function. With 2 or
more condition you can use SUMPRODUCT function


=SUMPRODUCT(('Car Inventory'!$K$2:K431>=01/01/00)*('Car
Inventory'!$K$2:K431<=12/31/00)*('Car Inventory'!$G$2:G431))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(YEAR('Car Inventory'!K2:K431)=2000),'Car Inventory'!G2:G431)

Biff

 

[SteveG]

You could use one of these.

=SUM(IF(YEAR('Car Inventory'!K2:K431)=2000,'Car Inventory'!G2:G431,0))

Which is an array formula so commit with Ctrl-Shift-Enter.

Or if you don't want to use an array formula then:

=SUMPRODUCT((YEAR('Car Inventory'!K2:K431)=2000)*('Car
Inventory'!G2:G431))

HTH

Steve

 

[Biff]

('Car Inventory'!$K$2:K431>=01/01/00)

That will be evaluated as:

'Car Inventory'!$K$2:K431>= 1 divided by 1 divided by 0.

Try it like this:

('Car Inventory'!$K$2:K431>=--"01/01/00")

Better yet, use the same syntax as the DATE( ) function:

('Car Inventory'!$K$2:K431>=--"2000/1/1")

OTOH, that looks kind of cryptic and ugly so maybe just use the DATE( )
function:

('Car Inventory'!$K$2:K431>=DATE(2000,1,1))

Biff

 

[nmtexman]

SUMPRODUCT does it for me. Thanks folks.