I have a database in Excel. Column A is Date in mm/dd/yyyy format.
Column G is Sales. I need a Sumproduct formula to return the sales
for Mondays in August 2009. How do I extract Mondays from mm/dd/yyyy
and build the formula? I do have multiple years in the dBase, so it
needs to consider the year too.

Thanks

One way:

=SUMPRODUCT(--(Weekday(Sheet2!A2:A11,2)=1),
--(text(Sheet2!A2:A11,"yyyymm")="200908"),
(Sheet2!G2:G11))


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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

wx4usa wrote:

I have a database in Excel. Column A is Date in mm/dd/yyyy format.
Column G is Sales. I need a Sumproduct formula to return the sales
for Mondays in August 2009. How do I extract Mondays from mm/dd/yyyy
and build the formula? I do have multiple years in the dBase, so it
needs to consider the year too.

Thanks

--

Dave Peterson

Try this...

=SUMPRODUCT(--(TEXT(A1:A500,"dddmmmyyyy")="MonAug2009"),G1:G500)

--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
...
I have a database in Excel. Column A is Date in mm/dd/yyyy format.
Column G is Sales. I need a Sumproduct formula to return the sales
for Mondays in August 2009. How do I extract Mondays from mm/dd/yyyy
and build the formula? I do have multiple years in the dBase, so it
needs to consider the year too.

Thanks

Consider using the function: WEEKDAY, YEAR, MONTH.
Micky


"wx4usa" wrote:

I have a database in Excel. Column A is Date in mm/dd/yyyy format.
Column G is Sales. I need a Sumproduct formula to return the sales
for Mondays in August 2009. How do I extract Mondays from mm/dd/yyyy
and build the formula? I do have multiple years in the dBase, so it
needs to consider the year too.

Thanks
.

Consider using the function: WEEKDAY, YEAR, MONTH.

I considered it but decided not to use those. Using the TEXT function
reduces the number of array tests that are needed so it's more efficient.

However, in some international locations the TEXT function returns "weird"
results for some date formats. So, if that method fails due to the location
of use then I would use some combination of WEEKDAY, YEAR, MONTH and/or
DATE.

--
Biff
Microsoft Excel MVP


"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Consider using the function: WEEKDAY, YEAR, MONTH.
Micky


"wx4usa" wrote:

I have a database in Excel. Column A is Date in mm/dd/yyyy format.
Column G is Sales. I need a Sumproduct formula to return the sales
for Mondays in August 2009. How do I extract Mondays from mm/dd/yyyy
and build the formula? I do have multiple years in the dBase, so it
needs to consider the year too.

Thanks
.