Find total based on a partial match

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,
 
C

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

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


Regards
Claus Busch
 

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

calculate total hours based on dates 5
partial match? 4
How to do an IF statement that copy data based on a partial match 0
sumproduct to calculate total based on non-continuous columns? 1
countif partial match 3
MATCH OR VLOOKUP 3
MATCH function not working 2
lookup and return column number 2

Top