Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF multiple conditions... open to suggestions (or PivotTable
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF multiple conditions... open to suggestions (or PivotTable
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 "Jamie W" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF multiple conditions... open to suggestions (or PivotTa
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 "rdwj" wrote: 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 "Jamie W" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF multiple conditions... open to suggestions (or PivotTa
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 On Jul 27, 9:24 pm, Jamie W wrote: 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 "rdwj" wrote: 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 "Jamie W" wrote: 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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF multiple conditions... open to suggestions (or PivotTa
Sorry, I got interrupted in the middle of that and didn't check it
thoroughly. The formula should be: =SUMPRODUCT((Dates-$A40)*(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 On Jul 28, 12:45 am, Pete_UK wrote: 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 On Jul 27, 9:24 pm, Jamie W wrote: 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 "rdwj" wrote: 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 "Jamie W" wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif multiple conditions | Excel Worksheet Functions | |||
CountIf multiple conditions | Excel Worksheet Functions | |||
countif-multiple conditions | Excel Discussion (Misc queries) | |||
COUNTIF with multiple conditions? | Excel Worksheet Functions | |||
COUNTIF with multiple conditions | Excel Discussion (Misc queries) |