Spreadsheet issue

W

whereisb

I have a spreadsheet which states either yes or no that a report was
filed on time and then following responses of whether the report was
complete or not, etc.

I need another worksheet in the same workbook to count the number of
'yes' responses but only if that yes was on a given date. For example,
for January 1, 2008 I had 5 reports filed. I had two yes and 3 no. On
the next page of the workbook I need to calculate the % of of yes and
no responses, but only for the specific date of 1-1-08.

How can I do this? PLEASE help!
 
T

Tyro

I'm not sure what you want. Do you have a column with a date, then a column
with yes/no to state whether the report was filed on time and another column
with yes/no to indicate if the report is complete? If so, which yes/no
column do you want to count?

Tyro
 
W

whereisb

Ok, here it is.

Column A Column B

Date Of Install On Time?
1 1/05/07 Y
2 1/05/07 Y
3 1/05/07 N
4 1/05/07 N
5 1/12/07 Y
6 1/12/07 N

SO...if you were to do a simple calculation to find the % that were on
time (Or not) for a certain date, you would see that for 1/05/07 there
would be 50% on time and 50% not since there are 4 completed reports
and 2 of yes and 2 of no were submitted. 2 yes divided by 4 total
reports = 50%. With me so far?

Now THAT part has worked so far (With the added detail that the
calculation is on another worksheet so I have to enter in the right
page information into the calculation. But the problem I am running
into is that the calculation is autmotically choosing a random a range
of cells as opposed to restricting itself to those of a certain date.
I need to eventually create a graph of each weeks' data. So the
calculation part saying 50% yes and 50% no is correct because I went
in and manually adjusted the range from say, A1 - A6 (Which is wrong
because A5 and A6 are using the date 1/12 and not 1/05) and I adjusted
it back to A1 - A4.

So what I am trying to do is add a second condition into a COUNTIF
statement saying count these numbers and give me a % but ONLY if it
matches a certain date, this way I dont have to go in and manually
adjust the range for each calculation.

I know I am doing a crappy job of explaining this. It's much easier
when you're looking at the workbook and I explain it face to face.
Hopefully this helps. If not, thanks anyway for trying!
 
P

Pete_UK

Suppose your data is on a sheet called Data, and in your summary sheet
you have dates in column A starting at A2, and column B is used for
the %age. Put this formula in B2:

=SUMPRODUCT((Data!A$2:A$1000=A2)*(Data!B$2:B$1000="Y") / COUNTIF(Data!A
$2:A$1000,A2)

I've assumed that you have up to 1000 rows of data in the Data sheet -
adjust the ranges if you have more. Format the cell as percentage and
copy down to cover your range of dates in the summary sheet.

Hope this helps.

Pete
 
Top