Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Counting items within a date range
I need to request a little help please:
I'm not fluent with writing formulas from scratch and need help (please): Using column B, I'd like to determine what was ordered by month and then categorize them by the item ordered. Ex: January and February, there was one for each item; in March there was 2 apples, 1 orange, 1 lemon and 1 pear; in April there was 1 apple, 2 oranges, 2 lemons and 2 pears. Column A Column B Ordered DateTime Apple 2008-01-04 15:32:18 Orange 2008-01-08 13:37:54 Lemon 2008-01-09 11:48:56 Pear 2008-01-09 16:33:48 Apple 2008-02-02 11:45:55 Orange 2008-02-04 09:52:51 Lemon 2008-02-05 10:05:49 Pear 2008-02-05 10:14:28 Apple 2008-03-03 18:34:04 Orange 2008-03-03 18:47:40 Lemon 2008-03-04 07:04:29 Pear 2008-03-04 10:58:00 Apple 2008-03-04 11:38:02 Orange 2008-04-10 13:46:23 Lemon 2008-04-10 14:18:13 Pear 2008-04-10 17:45:01 Apple 2008-04-11 08:40:02 Orange 2008-04-11 10:02:10 Lemon 2008-04-14 17:06:12 Pear 2008-04-15 09:40:26 The output would look like this: Item Jan Feb Mar Apr Apple 1 1 2 1 Orange 1 1 1 2 Lemon 1 1 1 2 Pear 1 1 1 2 Any help would be greatly appreciated...thank you, -- DS |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Counting items within a date range
=SUMPRODUCT(--($A$2:$A$200=$L2),--(TEXT($B$2:$B$200,"mmm")=M$1))
copy down and across -- __________________________________ HTH Bob "Dee Smith" wrote in message ... I need to request a little help please: I'm not fluent with writing formulas from scratch and need help (please): Using column B, I'd like to determine what was ordered by month and then categorize them by the item ordered. Ex: January and February, there was one for each item; in March there was 2 apples, 1 orange, 1 lemon and 1 pear; in April there was 1 apple, 2 oranges, 2 lemons and 2 pears. Column A Column B Ordered DateTime Apple 2008-01-04 15:32:18 Orange 2008-01-08 13:37:54 Lemon 2008-01-09 11:48:56 Pear 2008-01-09 16:33:48 Apple 2008-02-02 11:45:55 Orange 2008-02-04 09:52:51 Lemon 2008-02-05 10:05:49 Pear 2008-02-05 10:14:28 Apple 2008-03-03 18:34:04 Orange 2008-03-03 18:47:40 Lemon 2008-03-04 07:04:29 Pear 2008-03-04 10:58:00 Apple 2008-03-04 11:38:02 Orange 2008-04-10 13:46:23 Lemon 2008-04-10 14:18:13 Pear 2008-04-10 17:45:01 Apple 2008-04-11 08:40:02 Orange 2008-04-11 10:02:10 Lemon 2008-04-14 17:06:12 Pear 2008-04-15 09:40:26 The output would look like this: Item Jan Feb Mar Apr Apple 1 1 2 1 Orange 1 1 1 2 Lemon 1 1 1 2 Pear 1 1 1 2 Any help would be greatly appreciated...thank you, -- DS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Within a Date Range | Excel Worksheet Functions | |||
Counting unique items based on date | Excel Worksheet Functions | |||
Counting if between date range | Excel Worksheet Functions | |||
How do I count items within a date range in Excel? | Excel Worksheet Functions | |||
How to total items if they fall between a date range | Excel Discussion (Misc queries) |