|
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")) |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 03:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com