Conditional sums

[red386]

Hi there

I have a table that has statistics in it by date. I have simplified below. The yearNum, MonthNum and WeekNum are just forumla driven off the date.

The report is a variance report "as at" the date the user enters: Report for week ending: 17/05/2009

The reports are variance reports for the "month to the date" entered and the "year to date" against previous months and years. And yes, I know a database would be better, but my client is an Excel nut who will not accept calculations he cannot see.

I have used sumif calcs based on the month number to arrive at weekly variance calcs, however, I am not sure how to use Sumif to sum the column for all dates for the month where they are equal or less than the date entered for a year to date calculation. I have tried embedding sumifs, but this doesn't seem to work.

Any assistance you could provide would be great and much appreciated.

Thanks

Rebecca

 

[smiler44]

Rebecca,
Which of the two work books are you trying to add up, what range/cells are you trying to add up?
smiler44

 

[chergh]

In your "data format" workbook the following formula adds up all the dates less than the 10th of Jan

=SUMPRODUCT((F2:F16)*(A2:A16<DATE(2009,1,10)))

 

[red386]

Hi Smiler

I am trying to sum the ullage column(F) for the month to date and the year to date where the date of the statistic (Column A) is less than or equal to the date selected on the report page.

Any help you can provide would really help. Still trying to get the sumproduct suggestion to work.

Rebecca

 

[themurph2000]

In your "data format" workbook the following formula adds up all the dates less than the 10th of Jan

=SUMPRODUCT((F2:F16)*(A2:A16<DATE(2009,1,10)))

I'm hoping to use this same function myself. Unfortunately, some of the cells I use may have text in them, indicating that a particular day, something is scheduled to be set up. The other days (with numbers) refer to production goals for that day. I attached a sample of what I need to do.

Does anybody have any suggestions?

 

[themurph2000]

I'm hoping to use this same function myself. Unfortunately, some of the cells I use may have text in them, indicating that a particular day, something is scheduled to be set up. The other days (with numbers) refer to production goals for that day. I attached a sample of what I need to do.

Does anybody have any suggestions?

In the words of Emily Litella...

Never mind!

This formula

SUMPRODUCT((ISNUMBER(C7:H7))*(C6:H6<TODAY()),C7:H7)

will get you the sums of the numbers in range C7:H7 for any date in the range C6:H6 that was before today and not give an error if any cell in C7:H7 contains text.