To add month and year, and count of variances

=SUMPRODUCT(--(Sheet1!$A$1:$A$100)=DATE(2006,11,A1),--(Sheet1!$C$1:$C$1001)
)


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Secret Squirrel" wrote in
message ...
The format I have for the date is 11/01/06. So I would need to query the
entire date including the month and year. Also, the formula you wrote
doesn't
have the countif statement in it. I need to be able to count the number of
variances that are greater than 1, not a sum of the variances.

Column A Column B Column C
(actual) (Promised) (Variance)

11/01/06 11/03/06 -2

So what I need to do is count up the values in column C that are greater
than 1 for each day of the month. There will be multiple rows for each day
so
I need to summarize each day on a separate tab in the worksheet.

"Bob Phillips" wrote:

=SUMPRODUCT(--(DAY(Sheet1!$A$1:$A$100)=A1))

but what about the month being queried?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Secret Squirrel" wrote in
message ...
I have a worksheet that I am using to calculate on time delivery based
on
actual ship dates and promised ship dates. I have the countif function
calculating the two dates and giving me my result for each line item.
Now
what I need to do is summarize it by day of the month. I have another
worksheet that I'm using to summarize it. What I want to do is have
the
summary worksheet look at the ship date column (J) and count up all
the
variances greater than 2 in column (K). On the summary worksheet I
have a
column list of every day of that particular month so I want put the
total
for
each day next to the actual day on the summary tab. On the summary tab
column
(A) has the day of the month, column (B) is where I want it to put the
countif result for each day. How would I write this formula up?