Sumproduct Question

[wx4usa]

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]

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

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

 

[T. Valko]

Try this...

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[T. Valko]

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.