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?

=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?

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?

That formula may well not have COUNTIF in it, but it counts, it does not
sum.

--

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?

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?

I don't think it's working. It's returning a value of 0. It should be
returning a value of 1 since there is one variance that is greater than 1.

Here's what I have as the formula:

=SUMPRODUCT(--(Sheet1!$J$2:$J$143)=DATE(2006,11,2),--(Sheet1!$L$2:$L$1431))

"Bob Phillips" wrote:

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?

Column (L) is where I have the formula that is calculating the variance
between the two dates.

Here is that formula in case that could be causing the problem:

=IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1)

"Bob Phillips" wrote:

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?

I put a bracket in the wrong place

=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))

Shouldn't that be 2006,1,3 with your example data?

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
Column (L) is where I have the formula that is calculating the variance
between the two dates.

Here is that formula in case that could be causing the problem:

=IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1)

"Bob Phillips" wrote:

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?

That worked!

It should be 2006,11,2 (November 2, 2006) Why were you thinking it should be
2006,1,3?

"Bob Phillips" wrote:

I put a bracket in the wrong place

=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))

Shouldn't that be 2006,1,3 with your example data?

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
Column (L) is where I have the formula that is calculating the variance
between the two dates.

Here is that formula in case that could be causing the problem:

=IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1)

"Bob Phillips" wrote:

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?

Because your original example was

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

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


which is the 3rd.

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
That worked!

It should be 2006,11,2 (November 2, 2006) Why were you thinking it should
be
2006,1,3?

"Bob Phillips" wrote:

I put a bracket in the wrong place


=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))

Shouldn't that be 2006,1,3 with your example data?

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
Column (L) is where I have the formula that is calculating the
variance
between the two dates.

Here is that formula in case that could be causing the problem:

=IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1)

"Bob Phillips" wrote:

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?

Oh right, I guess I should have used the same dates in my examples. Sorry
about that. You were correct.

Thanks again for your help!

"Bob Phillips" wrote:

Because your original example was

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

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


which is the 3rd.

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
That worked!

It should be 2006,11,2 (November 2, 2006) Why were you thinking it should
be
2006,1,3?

"Bob Phillips" wrote:

I put a bracket in the wrong place


=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))

Shouldn't that be 2006,1,3 with your example data?

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
Column (L) is where I have the formula that is calculating the
variance
between the two dates.

Here is that formula in case that could be causing the problem:

=IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1)

"Bob Phillips" wrote:

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?

I have one followup question...

Say I want to add another array to this formula. I want it to only count up
the variances if column (K) has a 1 in it. How would I add this to the
formula you gave me? Or should I say how would you add it to the formula?

"Bob Phillips" wrote:

Because your original example was

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

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


which is the 3rd.

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
That worked!

It should be 2006,11,2 (November 2, 2006) Why were you thinking it should
be
2006,1,3?

"Bob Phillips" wrote:

I put a bracket in the wrong place


=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))

Shouldn't that be 2006,1,3 with your example data?

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
Column (L) is where I have the formula that is calculating the
variance
between the two dates.

Here is that formula in case that could be causing the problem:

=IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1)

"Bob Phillips" wrote:

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?

Just add it as another test, making sure that you use the same size range

=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))
),--(Sheet1!$K$2:$K$143=1))


--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
I have one followup question...

Say I want to add another array to this formula. I want it to only count
up
the variances if column (K) has a 1 in it. How would I add this to the
formula you gave me? Or should I say how would you add it to the formula?

"Bob Phillips" wrote:

Because your original example was

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

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


which is the 3rd.

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
That worked!

It should be 2006,11,2 (November 2, 2006) Why were you thinking it
should
be
2006,1,3?

"Bob Phillips" wrote:

I put a bracket in the wrong place



=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))

Shouldn't that be 2006,1,3 with your example data?

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote
in
message ...
Column (L) is where I have the formula that is calculating the
variance
between the two dates.

Here is that formula in case that could be causing the problem:

=IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1)

"Bob Phillips" wrote:

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?

got it! Thanks!

"Bob Phillips" wrote:

Just add it as another test, making sure that you use the same size range

=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))
),--(Sheet1!$K$2:$K$143=1))


--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
I have one followup question...

Say I want to add another array to this formula. I want it to only count
up
the variances if column (K) has a 1 in it. How would I add this to the
formula you gave me? Or should I say how would you add it to the formula?

"Bob Phillips" wrote:

Because your original example was

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

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


which is the 3rd.

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote in
message ...
That worked!

It should be 2006,11,2 (November 2, 2006) Why were you thinking it
should
be
2006,1,3?

"Bob Phillips" wrote:

I put a bracket in the wrong place



=SUMPRODUCT(--(Sheet1!$J$2:$J$143=DATE(2006,11,2)),--(Sheet1!$L$2:$L$1431))

Shouldn't that be 2006,1,3 with your example data?

--

HTH

Bob Phillips

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

"Secret Squirrel" wrote
in
message ...
Column (L) is where I have the formula that is calculating the
variance
between the two dates.

Here is that formula in case that could be causing the problem:

=IF(J2=I2,NETWORKDAYS(I2,J2)-1,NETWORKDAYS(I2,J2)+1)

"Bob Phillips" wrote:

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?