Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Find total based on a partial match
8 views
Skip to first unread message
cpliu
Jun 7, 2013, 10:48:31 AM6/7/13
to
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,
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
Jun 7, 2013, 11:13:14 AM6/7/13
to
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
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
=SUMPRODUCT(--(LEFT($C$2:$C$426,4)=LEFT(C2,4)),$F$2:$F$426)
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
0 new messages