| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is this possible? SUMPRODUCT with criteria range?
Hello again-
I have the following categories: (TIME) 12/31/2007 1/1/2008 1/2/2008 A 0800 1959 B 2000 0759 C 0000 1159 D 1200 2359 E 0700 1900 F 1900 0700 I also have 2 columns, 1 has a date and 1 the time. I want to count how many records meet the (1) date criteria and (2) fall within the time range...is this possible? I've used SUMPRODUCT before but never with a conditional like this. Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this possible? SUMPRODUCT with criteria range?
=SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=--(LEFT($A2,2)&":"&MID($A2,3,2))),--(Sheet1!$B$2:$B$20<=--(MID($A2,6,2)&":"&RIGHT($A2,2))))
-- __________________________________ HTH Bob "Cita" wrote in message ... Hello again- I have the following categories: (TIME) 12/31/2007 1/1/2008 1/2/2008 A 0800 1959 B 2000 0759 C 0000 1159 D 1200 2359 E 0700 1900 F 1900 0700 I also have 2 columns, 1 has a date and 1 the time. I want to count how many records meet the (1) date criteria and (2) fall within the time range...is this possible? I've used SUMPRODUCT before but never with a conditional like this. Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this possible? SUMPRODUCT with criteria range?
Thanks...but I can't get it to work. Maybe it's because I haven't used these
functions before. I keep getting the "#VALUE!" error when I try to modify it. Thanks again... "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=--(LEFT($A2,2)&":"&MID($A2,3,2))),--(Sheet1!$B$2:$B$20<=--(MID($A2,6,2)&":"&RIGHT($A2,2)))) -- __________________________________ HTH Bob "Cita" wrote in message ... Hello again- I have the following categories: (TIME) 12/31/2007 1/1/2008 1/2/2008 A 0800 1959 B 2000 0759 C 0000 1159 D 1200 2359 E 0700 1900 F 1900 0700 I also have 2 columns, 1 has a date and 1 the time. I want to count how many records meet the (1) date criteria and (2) fall within the time range...is this possible? I've used SUMPRODUCT before but never with a conditional like this. Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this possible? SUMPRODUCT with criteria range?
Tell me how and why you are modifying it.
-- __________________________________ HTH Bob "Cita" wrote in message ... Thanks...but I can't get it to work. Maybe it's because I haven't used these functions before. I keep getting the "#VALUE!" error when I try to modify it. Thanks again... "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=--(LEFT($A2,2)&":"&MID($A2,3,2))),--(Sheet1!$B$2:$B$20<=--(MID($A2,6,2)&":"&RIGHT($A2,2)))) -- __________________________________ HTH Bob "Cita" wrote in message ... Hello again- I have the following categories: (TIME) 12/31/2007 1/1/2008 1/2/2008 A 0800 1959 B 2000 0759 C 0000 1159 D 1200 2359 E 0700 1900 F 1900 0700 I also have 2 columns, 1 has a date and 1 the time. I want to count how many records meet the (1) date criteria and (2) fall within the time range...is this possible? I've used SUMPRODUCT before but never with a conditional like this. Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this possible? SUMPRODUCT with criteria range?
The only things I changed were the cell references ie my data ranges to be
analyzed are in F and G and the criteria (ranges) are in J and K... "Bob Phillips" wrote: Tell me how and why you are modifying it. -- __________________________________ HTH Bob "Cita" wrote in message ... Thanks...but I can't get it to work. Maybe it's because I haven't used these functions before. I keep getting the "#VALUE!" error when I try to modify it. Thanks again... "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=--(LEFT($A2,2)&":"&MID($A2,3,2))),--(Sheet1!$B$2:$B$20<=--(MID($A2,6,2)&":"&RIGHT($A2,2)))) -- __________________________________ HTH Bob "Cita" wrote in message ... Hello again- I have the following categories: (TIME) 12/31/2007 1/1/2008 1/2/2008 A 0800 1959 B 2000 0759 C 0000 1159 D 1200 2359 E 0700 1900 F 1900 0700 I also have 2 columns, 1 has a date and 1 the time. I want to count how many records meet the (1) date criteria and (2) fall within the time range...is this possible? I've used SUMPRODUCT before but never with a conditional like this. Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this possible? SUMPRODUCT with criteria range?
Post it.
-- __________________________________ HTH Bob "Cita" wrote in message ... The only things I changed were the cell references ie my data ranges to be analyzed are in F and G and the criteria (ranges) are in J and K... "Bob Phillips" wrote: Tell me how and why you are modifying it. -- __________________________________ HTH Bob "Cita" wrote in message ... Thanks...but I can't get it to work. Maybe it's because I haven't used these functions before. I keep getting the "#VALUE!" error when I try to modify it. Thanks again... "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=--(LEFT($A2,2)&":"&MID($A2,3,2))),--(Sheet1!$B$2:$B$20<=--(MID($A2,6,2)&":"&RIGHT($A2,2)))) -- __________________________________ HTH Bob "Cita" wrote in message ... Hello again- I have the following categories: (TIME) 12/31/2007 1/1/2008 1/2/2008 A 0800 1959 B 2000 0759 C 0000 1159 D 1200 2359 E 0700 1900 F 1900 0700 I also have 2 columns, 1 has a date and 1 the time. I want to count how many records meet the (1) date criteria and (2) fall within the time range...is this possible? I've used SUMPRODUCT before but never with a conditional like this. Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this possible? SUMPRODUCT with criteria range?
Here it is...
A B C D E F G H I J K L xx xx xx xx xx 12/31 0040 12/31 01/01 01/02 12/31 1720 0900 2059 * * * 2100 0859 * * * Columns F and G hold the data I have to sort through using the SUMPRODUCT function. H and I hold the "categories" or ranges that column G need to fall between to be classified under the corresponding date (J, K, L, etc.). In other words, the entry in F1/G1 would be counted for the "2100/0859" category in column J... Does this help? "Bob Phillips" wrote: Post it. -- __________________________________ HTH Bob "Cita" wrote in message ... The only things I changed were the cell references ie my data ranges to be analyzed are in F and G and the criteria (ranges) are in J and K... "Bob Phillips" wrote: Tell me how and why you are modifying it. -- __________________________________ HTH Bob "Cita" wrote in message ... Thanks...but I can't get it to work. Maybe it's because I haven't used these functions before. I keep getting the "#VALUE!" error when I try to modify it. Thanks again... "Bob Phillips" wrote: =SUMPRODUCT(--(Sheet1!$A$2:$A$20=B$1),--(Sheet1!$B$2:$B$20=--(LEFT($A2,2)&":"&MID($A2,3,2))),--(Sheet1!$B$2:$B$20<=--(MID($A2,6,2)&":"&RIGHT($A2,2)))) -- __________________________________ HTH Bob "Cita" wrote in message ... Hello again- I have the following categories: (TIME) 12/31/2007 1/1/2008 1/2/2008 A 0800 1959 B 2000 0759 C 0000 1159 D 1200 2359 E 0700 1900 F 1900 0700 I also have 2 columns, 1 has a date and 1 the time. I want to count how many records meet the (1) date criteria and (2) fall within the time range...is this possible? I've used SUMPRODUCT before but never with a conditional like this. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
| Sumproduct 4 criteria | Excel Worksheet Functions | |||
| Sumproduct-multiple criteria for same range "OR" | Excel Worksheet Functions | |||
| Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
| Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) | |||
Linear Mode
