| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Question....
I have a column (c) with dates...I have column (ba) with text data, sometimes
RIEP sometimes Charged with (some type of crime). I want to have excel add up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 - 3/31/2006). I am able to do this with the following formula. I then want excel to add up the occurances of Charged. I have tried to insert "Charged*" ,but this does not work. Is there a better formula for this type of action?? I have to havce a variable because I want to be able to have data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with...... what ever.... =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31")) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Question....
try:
=SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=--"2006-01-01"), --(CaseData!$C$2:$C$1000<=--"2006-03-31")) (the asterisks have been replaced with a comma (use your list separator).) Personally, I'd be more explicit with the dates: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=date(2006,1,1)), --(CaseData!$C$2:$C$1000<=date(2006,03,31)) I don't trust excel to get the ymd correct. Jeremy Ellison wrote: I have a column (c) with dates...I have column (ba) with text data, sometimes RIEP sometimes Charged with (some type of crime). I want to have excel add up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 - 3/31/2006). I am able to do this with the following formula. I then want excel to add up the occurances of Charged. I have tried to insert "Charged*" ,but this does not work. Is there a better formula for this type of action?? I have to havce a variable because I want to be able to have data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with...... what ever.... =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31")) -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Question....
That worked, but I am still trying to find out how to put a wildcard into the
formlua to look at anything beginning with charged.... charged VOCSL1 or charged VOCSL3 or someother charged..... "Dave Peterson" wrote: try: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=--"2006-01-01"), --(CaseData!$C$2:$C$1000<=--"2006-03-31")) (the asterisks have been replaced with a comma (use your list separator).) Personally, I'd be more explicit with the dates: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=date(2006,1,1)), --(CaseData!$C$2:$C$1000<=date(2006,03,31)) I don't trust excel to get the ymd correct. Jeremy Ellison wrote: I have a column (c) with dates...I have column (ba) with text data, sometimes RIEP sometimes Charged with (some type of crime). I want to have excel add up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 - 3/31/2006). I am able to do this with the following formula. I then want excel to add up the occurances of Charged. I have tried to insert "Charged*" ,but this does not work. Is there a better formula for this type of action?? I have to havce a variable because I want to be able to have data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with...... what ever.... =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31")) -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Question....
You can try this:
=SUMPRODUCT((ISNUMBER(SEARCH("Charged",CaseData!$B A$2:$BA$1000)))*(CaseData!$C$2:$C$1000=DATE(2006, 1,1))*(CaseData!$C$2:$C$1000<=DATE(2006,3,31))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jeremy Ellison" wrote in message ... That worked, but I am still trying to find out how to put a wildcard into the formlua to look at anything beginning with charged.... charged VOCSL1 or charged VOCSL3 or someother charged..... "Dave Peterson" wrote: try: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=--"2006-01-01"), --(CaseData!$C$2:$C$1000<=--"2006-03-31")) (the asterisks have been replaced with a comma (use your list separator).) Personally, I'd be more explicit with the dates: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=date(2006,1,1)), --(CaseData!$C$2:$C$1000<=date(2006,03,31)) I don't trust excel to get the ymd correct. Jeremy Ellison wrote: I have a column (c) with dates...I have column (ba) with text data, sometimes RIEP sometimes Charged with (some type of crime). I want to have excel add up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 - 3/31/2006). I am able to do this with the following formula. I then want excel to add up the occurances of Charged. I have tried to insert "Charged*" ,but this does not work. Is there a better formula for this type of action?? I have to havce a variable because I want to be able to have data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with...... what ever.... =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31")) -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Question....
Thanks RD.
(I missed that portion.) Ragdyer wrote: You can try this: =SUMPRODUCT((ISNUMBER(SEARCH("Charged",CaseData!$B A$2:$BA$1000)))*(CaseData!$C$2:$C$1000=DATE(2006, 1,1))*(CaseData!$C$2:$C$1000<=DATE(2006,3,31))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jeremy Ellison" wrote in message ... That worked, but I am still trying to find out how to put a wildcard into the formlua to look at anything beginning with charged.... charged VOCSL1 or charged VOCSL3 or someother charged..... "Dave Peterson" wrote: try: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=--"2006-01-01"), --(CaseData!$C$2:$C$1000<=--"2006-03-31")) (the asterisks have been replaced with a comma (use your list separator).) Personally, I'd be more explicit with the dates: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=date(2006,1,1)), --(CaseData!$C$2:$C$1000<=date(2006,03,31)) I don't trust excel to get the ymd correct. Jeremy Ellison wrote: I have a column (c) with dates...I have column (ba) with text data, sometimes RIEP sometimes Charged with (some type of crime). I want to have excel add up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 - 3/31/2006). I am able to do this with the following formula. I then want excel to add up the occurances of Charged. I have tried to insert "Charged*" ,but this does not work. Is there a better formula for this type of action?? I have to havce a variable because I want to be able to have data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with...... what ever.... =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31")) -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Question....
I thought you were done for the day.<g
Looks like you're trying to duplicate Frank's old habits (God rest his soul). -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... Thanks RD. (I missed that portion.) Ragdyer wrote: You can try this: =SUMPRODUCT((ISNUMBER(SEARCH("Charged",CaseData!$B A$2:$BA$1000)))*(CaseData!$C$2:$C$1000=DATE(2006, 1,1))*(CaseData!$C$2:$C$1000<=DATE(2006,3,31))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jeremy Ellison" wrote in message ... That worked, but I am still trying to find out how to put a wildcard into the formlua to look at anything beginning with charged.... charged VOCSL1 or charged VOCSL3 or someother charged..... "Dave Peterson" wrote: try: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=--"2006-01-01"), --(CaseData!$C$2:$C$1000<=--"2006-03-31")) (the asterisks have been replaced with a comma (use your list separator).) Personally, I'd be more explicit with the dates: =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP"), --(CaseData!$C$2:$C$1000=date(2006,1,1)), --(CaseData!$C$2:$C$1000<=date(2006,03,31)) I don't trust excel to get the ymd correct. Jeremy Ellison wrote: I have a column (c) with dates...I have column (ba) with text data, sometimes RIEP sometimes Charged with (some type of crime). I want to have excel add up the total occurrances of RIEP between 2 dates (ie quarter 1 = 1/1/2006 - 3/31/2006). I am able to do this with the following formula. I then want excel to add up the occurances of Charged. I have tried to insert "Charged*" ,but this does not work. Is there a better formula for this type of action?? I have to havce a variable because I want to be able to have data such as Charged with VOCSL-1 Charged with VOCSL-1 charged with...... what ever.... =SUMPRODUCT(--(CaseData!$BA$2:$BA$1000="RIEP")*--(CaseData!$C$2:$C$1000=--"2006-01-01")*--(CaseData!$C$2:$C$1000<=--"2006-03-31")) -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| another sumproduct question | Excel Worksheet Functions | |||
| another sumproduct question | Excel Worksheet Functions | |||
| SUMPRODUCT Question... | Excel Discussion (Misc queries) | |||
| Question about sumproduct | Excel Discussion (Misc queries) | |||
| Question about sumproduct | Excel Discussion (Misc queries) | |||
Linear Mode
