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
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!
Pete_UK - 06 Feb 2008 15:41 GMT
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
On Feb 6, 2:46 pm, where...@whereisbrian.com wrote:
> Ok, here it is.
>
[quoted text clipped - 33 lines]
> when you're looking at the workbook and I explain it face to face.
> Hopefully this helps. If not, thanks anyway for trying!