SUM IF returns a zero value
 

I have a gannt chart/programme on Page 1 which uses NOW() to calculate
programmed progress of each work activity according to the number of days
after the start date. I also have a column where the estimate of work is
manually entered.

I also have a chart showing the total number of task weeks completed on the
vertical axis and the weekly commencing date along the horizontal. I want
the chart to plot the programmed vs actual progress of the work.

I am having problems with the values for the chart. I have created a second
sheet to calculate the work carried out within specific weeks, but the
numbers don't add up.

I use the following function to calculate a running total of programmed
progress:
=SUMIF('Page 1'!$F$4:$F$260,"<="&A25,'Page 1'!$D$4:$D$260)/6

Page 1 Col F is the end date
A25 is the week commencing date
Page 1 Col D is the Total activity days for each activity.

Both, Sheet 1 and Sheet 2 have the same total for the last week of the
project, 708 weeks.
Sheet 1 shows 307 programmed weeks completed to date, but Sheet 2 has a
value of 251 for the week 03 Spet 07..
Sheet 2 remember is a running total, and the value of 307 doesn't appear
until 01/October/07.

The values for the running totals of actual progress do not add up either.

Using
=SUMIF('Page 1'!$E$4:$E$260,"="&A2,'Page 1'!$J$4:$J$260)-SUMIF('Page
1'!$E$4:$E$260,""&A2,'Page 1'!$J$4:$J$260)

To subtract the values between start and end dates in Sheet 1 results in a
figure which is light.
The programmed total value of actual work is 347 weeks, but the running
total value is 99.95.

This is quite complicated, hopefully, two head being better than one, we can
get the chart to reflect the programmed progress against actual.

SUM IF returns a zero value
 

I would trychanging the ranges to identify specific rows with non matching
data ranges then look at individual cells in that row to find the difference

you may have some entries as text that make your totals be low.
or your data may not truely be duplicated. (decimal point differences etc.)

"dd" wrote:

I have a gannt chart/programme on Page 1 which uses NOW() to calculate
programmed progress of each work activity according to the number of days
after the start date. I also have a column where the estimate of work is
manually entered.

I also have a chart showing the total number of task weeks completed on the
vertical axis and the weekly commencing date along the horizontal. I want
the chart to plot the programmed vs actual progress of the work.

I am having problems with the values for the chart. I have created a second
sheet to calculate the work carried out within specific weeks, but the
numbers don't add up.

I use the following function to calculate a running total of programmed
progress:
=SUMIF('Page 1'!$F$4:$F$260,"<="&A25,'Page 1'!$D$4:$D$260)/6

Page 1 Col F is the end date
A25 is the week commencing date
Page 1 Col D is the Total activity days for each activity.

Both, Sheet 1 and Sheet 2 have the same total for the last week of the
project, 708 weeks.
Sheet 1 shows 307 programmed weeks completed to date, but Sheet 2 has a
value of 251 for the week 03 Spet 07..
Sheet 2 remember is a running total, and the value of 307 doesn't appear
until 01/October/07.

The values for the running totals of actual progress do not add up either.

Using
=SUMIF('Page 1'!$E$4:$E$260,"="&A2,'Page 1'!$J$4:$J$260)-SUMIF('Page
1'!$E$4:$E$260,""&A2,'Page 1'!$J$4:$J$260)

To subtract the values between start and end dates in Sheet 1 results in a
figure which is light.
The programmed total value of actual work is 347 weeks, but the running
total value is 99.95.

This is quite complicated, hopefully, two head being better than one, we can
get the chart to reflect the programmed progress against actual.