Find total based on a partial match

  • Thread starter Thread starter cpliu
  • Start date Start date
C

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,
 
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
Click to expand...

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


Regards
Claus Busch
 
Back
Top