Date issue

[Dick]

=SUMPRODUCT((Sheet1!A:A="Alkalume 143")*(Sheet1!B:B="January"),Sheet1!
D)
Is there a way to format Sheet1!B:B as an ACTUAL DATE with days, like
1/5/2011 and still work the same. Currently I have Column B formatted
as text and Column C (the day) formatted as text. It works okay this
way but kind of sloppy.
Thanks in advance for any help!!!

 

[Dave Peterson]

You still can't use the entire column in xl2003 and below.

=SUMPRODUCT(--(Sheet1!A1:A111="Alkalume 143"),
--(month(Sheet1!B1:B111)=1),
Sheet1!D1111)


If you wanted to just check January of 2003, you could use something like:

=SUMPRODUCT(--(Sheet1!A1:A111="Alkalume 143"),
--(text(Sheet1!B1:B111,"yyyymm")="200301"),
Sheet1!D1111)

ps.

Empty cells in B1:B111 will be treated as January 0, 1900 (if you're using 1900
as the base year.

You can modify your formula for plain old January so that those empty cells are
ignored:


=SUMPRODUCT(--(Sheet1!A1:A111="Alkalume 143"),
--(month(Sheet1!B1:B111)=1),
--(isnumber(sheet1!b1:b111)),
Sheet1!D1111)


Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html