Hi All,

I've a Issue with framing a fomula for the below Scenario in Excel:

1) I have 1 row which has week number like 40-06, 41-06, etc... in one row
which keeps repeating for all pass fail block column for 7 days.1
2) I have 1 row below which has Pass, Fail, Block which repeats for every
day as column heading.
3) The above is for a daily bais and for every month, so 12 sheets with 30
sets of pass fail block column and each column above has the week number as
mentioned above.

The Problem:
I would like to calculate the weekly report for the same group that is pass
fail block based on the week number and the pass fail block criteria. The
problem is because some days in a week will have 2 days in one month and the
balance in next month. Could u please suggest a formula or method for the
above. It is Urgent

Ramesh,

Start over, with one sheet, and a database. Multiple sheets, multiple columns for one day, etc, are
all just impediments to easy use of your data.

Use dates in one column. Use three rows for each date.
The seond column should have Pass, Fail, Block repeated...
The third column should have the data - the count, or whatever you use that you want summed.
You can have other date information like day of week, week number, month, year, etc in additional
columns, but those are not necessary for Excel.

Then use a pivot table with date as the row field, and the second column as the column Field, and
yourthird column as the data field. (You can also use filters to summarize/show the data that you
are interested in.) Excel can summarize the data in a pivot table by week, using the grouping
functionality. No formulas, no errors.

HTH,
Bernie
MS Excel MVP


"Ramesh.S, India" wrote in message
...
Hi All,

I've a Issue with framing a fomula for the below Scenario in Excel:

1) I have 1 row which has week number like 40-06, 41-06, etc... in one row
which keeps repeating for all pass fail block column for 7 days.1
2) I have 1 row below which has Pass, Fail, Block which repeats for every
day as column heading.
3) The above is for a daily bais and for every month, so 12 sheets with 30
sets of pass fail block column and each column above has the week number as
mentioned above.

The Problem:
I would like to calculate the weekly report for the same group that is pass
fail block based on the week number and the pass fail block criteria. The
problem is because some days in a week will have 2 days in one month and the
balance in next month. Could u please suggest a formula or method for the
above. It is Urgent