Sumproduct with weekend day test

[Loge]

A2:A32 contains dates (in proper date-number format), and may contain up to 3
blanks (from A30:A32) depending on the length of the month in question.
C2:C32 contains names and will have the same number of blanks as the date
range.
A39 contains "H"
I need to find the number of occurrences where "H" is in the C column and
the date on the same line in the A column represents a weekend day.

I started to build the formula using only Saturday as a test but
=SUMPRODUCT(--(C2:C32=A39),(WEEKDAY(A2:A32,2)=6)) retuned a zero when there
was an "H" in the same row as Nov 15, 2008 (Saturday). The first array works
fine by itself, but I clearly don't understand how to set up the second aray.

Please show me the correct way, especially to include both Satudays and
Sundays in the result.

Thanks!

 

[T. Valko]

Try this:

=SUMPRODUCT(--(WEEKDAY(A2:A32,2)>5),--(C2:C32=A39))

 

[Ken Johnson]

A2:A32 contains dates (in proper date-number format), and may contain up to 3
blanks (from A30:A32) depending on the length of the month in question.
C2:C32 contains names and will have the same number of blanks as the date
range.
A39 contains "H"
I need to find the number of occurrences where "H" is in the C column and
the date on the same line in the A column represents a weekend day.

I started to build the formula using only Saturday as a test but
=SUMPRODUCT(--(C2:C32=A39),(WEEKDAY(A2:A32,2)=6)) retuned a zero when there
was an "H" in the same row as Nov 15, 2008 (Saturday).  The first arrayworks
fine by itself, but I clearly don't understand how to set up the second aray.

Please show me the correct way, especially to include both Satudays and
Sundays in the result.

Thanks!

=SUMPRODUCT((C2:C32=A39)*(WEEKDAY(A2:A32,2)>5))

or include the double unary for the 2nd argument...

=SUMPRODUCT(--(C2:C32=A39),--(WEEKDAY(A2:A32,2)>5))

Ken Johnson

 

[Loge]

Perfect - Thanks!

I don't understand the double unary. What, exactly, is the math behind it?
Do you have a good reference on the double unary or the SUMPRODUCT function?

 

[T. Valko]

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html