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
|