COUNTIF multiple conditions... open to suggestions (or PivotTable

[Guest]

Hi All,

Currently, I'm trying to set up a data table for records which, once it
works, should resemble the following:

Entered Received
0-30 31-60 61-90 >90 0-30 31-60 61-90 >90
-----------------------------------------------------
7/27/2007 |35 | 192 | 21 | 20 | 15 | 45 | 38 | 12
8/3/2007 (etc)--->
The rows correspond to dates set one week apart, while the columns are
broken down to four attributes (E, R, I) each with four "age" classficiations
(0-30 days and so on, as noted). All the data that will ultimately populate
this table comes from another sheet which has a couple thousand rows of data
with corresponding text values and dates. (IE: 6/28/2007 | Entered |, or
6/29/2007 | Received, where "|" delineates column breaks for the purpose of
demonstration.)

How would I go about creating either an array formula or even using a
PivotTable to count any rows from the data sheet which have a date that's,
say, 30 days or less from the date corresponding to the row as shown in the
first example AND has a certain text value as well?

Kinda confusing, I know. If I was doing this in SQL it'd be cake, but
Excel's not so friendly for the purpose. Basically, here's the goal in
relative terms... let's say I have a bunch of customers returning any bad
apples, and the process flow dictates that the return request goes from
Entered to Received to Inspected. I'm trying to determine how many apples
are in each state and for how long in relation to the date I have showing for
each row.

ANY help would be greatly appreciated, I've been spending the last hour or
so trying to figure this out without any luck.

Thanks,

Jamie

 

[Guest]

You are trying to mix a Frequency formula with other logic, which is
difficult in one step.
If nothing else helpes, I would suggest massaging your data a bit to make it
easier to review - indeed through a pivot table.
To do that, you need to convert the dates into a date that shows the week
that it falls in. For example =A2-WEEKDAY(A2). Enter this in column C, and
column C will show the first day of the week that the data comes from

You do not show how your date is derived - is this available in the data or
do you need to calculate it. Use a bit of imagination to standardise your
data into something that you can use to count. Look at the FREQUENCY formula
as it might be able to assist.

Once the data is standardised, you might want to use a Pivot.

rdwj

 

[Guest]

Thanks rdwj:

The dates for the source data are present, as are the dates for the
presenting table.
The only date that would need to be derived is the -30/-60/-90/->90 I'd
mentioned; I know Excel is able to actively interpret an invalid date into a
proper one (IE: January 35th as February 4th), but I wasn't sure if there's
a function where you can direct it to include any data x number of days prior
to a date. Unfortunately, the format I'm working with currently is the
result of massaging the data... I'm trying to interpret the original block
into a format which will be graphic-friendly, since this is for
presentational purposes.

I checked out the Frequency formula, and it looks like you were right, this
would be helpful for the solution... the only hitch I can see is that I don't
know if again, Excel will recognize a date formula as the stipulated range...
IE "0-30 days from x date", "31-60 days from x date" etc. If Excel readily
recognized Julian dates it would make this easier, but using that as the
benchmark would just throw another formula into the fray to have Excel
interpret Julian dates to Gregorian (or vice versa).

Thanks again!

Jamie

 

[Pete_UK]

Assume in your main data table that you define two named ranges -
"Dates" and "Category" to cover the obvious. In your other sheet,
assume your first date is in A4. Then you can use a formula like this
to count:

=SUMPRODUCT((Dates-$A4<=0)*(Dates-$A4>=30)*(Category="E"))

I'm not sure if I am counting the dates the correct way (is it 30 days
earlier or later?), but hopefully you can see that this is comparing
dates between 0-30 days and you can see what to do for 31-60 and 61-90
days. This will count the relevant dates in category E.

It is better to replace 0, 30 and E with cell references, and you will
have these in the header block above your data - might be A$3, B$3 and
$C$2 respectively, but this depends on your exact layout. If you set
this up correctly then you will be able to copy this formula across to
cover the 3 day ranges for category "E". Then a similar formula but
looking at category "I" (eg $F$2) can be used for the next 3 columns
and so on across the first row.

Then the 12 formulae can be copied down for as many rows as you have
in your summary table.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, I got interrupted in the middle of that and didn't check it
thoroughly. The formula should be:

=SUMPRODUCT((Dates-$A4>0)*(Dates-$A4<=30)*(Category="E"))

although you may have to make it $A4-Dates rather than Dates-$A4
depending on whether the dates are before or after A4.

Hope this helps.

Pete