Find total based on a partial match

[cpliu]

I'm trying to find the total time on all projects with identical initial code.
For example in column C, I have:
SHOG1001
SHOG1002
SHOG1003
ABCD1234
BCDE1234
BBBB3333
....

I'd like to find the total time spent (each time is in F column) on projects staring with SHOG. In the example above, it would the first 3.

I tried something like the one below, but all I got is N/A.

=SUMPRODUCT((A$2:A426=A444),(C$2:C426="SHOG*")*(F$2:F426))

Thanks for your help,

 

[Claus Busch]

Hi,

Am Fri, 7 Jun 2013 07:48:31 -0700 (PDT) schrieb cpliu:

I'm trying to find the total time on all projects with identical initial code.
For example in column C, I have:
SHOG1001
SHOG1002
SHOG1003
ABCD1234
BCDE1234
BBBB3333

try:
=SUMPRODUCT(--(LEFT($C$2:$C$426,4)=LEFT(C2,4)),$F$2:$F$426)


Regards
Claus Busch