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)

The one in red does not work. Any suggestions?


--
nmtexman
------------------------------------------------------------------------
nmtexman's Profile: http://www.excelforum.com/member.php...o&userid=35684
View this thread: http://www.excelforum.com/showthread...hreadid=554664

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

Hi!

Try this:

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

Biff

"nmtexman" wrote in
message ...

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)

The one in red does not work. Any suggestions?


--
nmtexman
------------------------------------------------------------------------
nmtexman's Profile:
http://www.excelforum.com/member.php...o&userid=35684
View this thread: http://www.excelforum.com/showthread...hreadid=554664

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=554664

('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

"Franz Verga" wrote in message
...
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

SUMPRODUCT does it for me. Thanks folks.:)


--
nmtexman
------------------------------------------------------------------------
nmtexman's Profile: http://www.excelforum.com/member.php...o&userid=35684
View this thread: http://www.excelforum.com/showthread...hreadid=554664