=SUM(IF(($C$3:$C$26=G3)*(left($E$3:$E$26,8)="Compl ete"),$D$3:$D$26))
(still array entered)

You could also:
=SUMPRODUCT(--($C$3:$C$26=G3),--(LEFT($E$3:$E$26,8)="Complete"),$D$3:$D$26)
and enter it as a normal formula.

If you were looking for Complete anywhere in E3:E26, you could use:

=SUMPRODUCT(--($C$3:$C$26=G3),
--(ISNUMBER(SEARCH("complete",$E$3:$E$26))),
$D$3:$D$26)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Jim@Tech wrote:

Hi,

I'm trying to do something really similar but can't get it working, I'd be
really grateful if someone could explain why.

I've got a list of tasks in column B, names of people working on those tasks
in column C, the amount of effort in column D and the deadline in column E
but this is complicated by the fact that some tasks are complete and so have
"Complete" entered into the deadline column and some of the complete tasks
also include the date the task was completed in this column e.g. "Complete
02/2009".

In column G I've got a list of the unique name values and I need to sum the
amount of effort corresponding to completed tasks for each name. I've tried a
coupe of different formulas (listed below) to do this but cannot get it to
work propertly:-

This works if I delete all the date info from the completed tasks so that
they only have "Complete" in column E:

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26="Complete"),$ D$3:$D$26))}

I thought it would be easy to just add a "*" into the formula so that it
would sum all effort for any row where the value in column E starts with the
word "complete":

{=SUM(IF(($C$3:$C$26=G3)*($E$3:$E$26="Complete*"), $D$3:$D$26))}

But this returns 0 for all anmes and I know this is not correct.

Following the logic above, I tried this:

{=SUM(--(IF(($C$3:$C$26=G3)*($E$3:$E$26="Complete*"),$D$3: $D$26)))}

but this also returns 0 for all names.

What am I doing wrong!

Thanks,
Jim.

"Sloth" wrote:

the "--" is used to convert an array of logical values (ie {TRUE, TRUE,
FALSE, TRUE}), to an array of 1's and 0's (ie {1,1,0,1}). This is necessary
because SUMPRODUCT will skip anything other than numerical values (like the
SUM function).

"fractallinda" wrote:

terrific!! It worked great!
What is the "--" ? I had tried that formula without -- and it didn't work.
Thank you SO much

"fractallinda" wrote:

I'm trying to get the sum of a list of numbers that start with a certain
number (in this example 1). I can't seem to make any wildcard work. My
numbers all have 5 digits so I've tried 1* and 1????.
=SUMIF(A1:A307,"1*",F1:F307)
=SUMIF(A1:A307,"1????",F1:F307)
don't work. Will someone please tell me what I'm doing incorrectly? Thanks
in advance.

--

Dave Peterson