SUMPRODUCT Question....

G

Guest

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"))
 
D

Dave Peterson

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.
 
G

Guest

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.....
 
R

Ragdyer

You can try this:

=SUMPRODUCT((ISNUMBER(SEARCH("Charged",CaseData!$BA$2:$BA$1000)))*(CaseData!$C$2:$C$1000>=DATE(2006,1,1))*(CaseData!$C$2:$C$1000<=DATE(2006,3,31)))
 
D

Dave Peterson

Thanks RD.

(I missed that portion.)
You can try this:

=SUMPRODUCT((ISNUMBER(SEARCH("Charged",CaseData!$BA$2:$BA$1000)))*(CaseData!$C$2:$C$1000>=DATE(2006,1,1))*(CaseData!$C$2:$C$1000<=DATE(2006,3,31)))
 
R

Ragdyer

I thought you were done for the day.<g>

Looks like you're trying to duplicate Frank's old habits (God rest his
soul).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top