partial match?

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

cpliu

=SUMPRODUCT(($B$2:$B$20=$E9)*($C$2:$C$20=F$1)*($D$2:$D$20))

In sumproduct above, the text has to match exactly. Is there a way to
match partially (F$1) such as containing a word "develop" then it
would be added together?

Thanks,
 
cpliu said:
=SUMPRODUCT(($B$2:$B$20=$E9)*($C$2:$C$20=F$1)*($D$2:$D$20))
In sumproduct above, the text has to match exactly.
Is there a way to match partially (F$1) such as containing
a word "develop" then it would be added together?
Click to expand...

Not exactly sure what you mean. Perhaps:

=SUMPRODUCT(($B$2:$B$20=$E9)*ISNUMBER(FIND(F$1,$C$2:$C$20)),$D$2:$D$20)

Note: See the Help pages for the difference between FIND and SEARCH. You
might prefer SEARCH, depending on your requirements.
 
Not exactly sure what you mean.  Perhaps:

=SUMPRODUCT(($B$2:$B$20=$E9)*ISNUMBER(FIND(F$1,$C$2:$C$20)),$D$2:$D$20)
Click to expand...

sorry I wasn't clear. I'm trying to do project management with Excel.
There are multiple tasks under the same category development; eg.
develop- graphics, develop- media, develop- audio. I would like them
to caculate the total time spent on develop. As long as there is a
match in "develop", it would be summed together. can I do that?

Thanks,
 
Don't you just want Sumif, as in:
=sumif(b:b,"*develop*",d:d)
Adjust the ranges to suit.

Regards,
Fred

Not exactly sure what you mean. Perhaps:

=SUMPRODUCT(($B$2:$B$20=$E9)*ISNUMBER(FIND(F$1,$C$2:$C$20)),$D$2:$D$20)
Click to expand...

sorry I wasn't clear. I'm trying to do project management with Excel.
There are multiple tasks under the same category development; eg.
develop- graphics, develop- media, develop- audio. I would like them
to caculate the total time spent on develop. As long as there is a
match in "develop", it would be summed together. can I do that?

Thanks,
 
cpliu said:
As long as there is a match in "develop", it
would be summed together. can I do that?
Click to expand...

Sure. But you would have to explain why you cannot adapt my previous answer
to fit your situation.

Alternatively, if you are looking for a turnkey solution -- something that
you can copy-and-paste into your worksheet -- dontcha think it makes sense
that you need to provide more details?

I notice that Fred offered a solution using SUMIF. I agree that that would
be a better solution if you truly want to sum things only ``as long as there
is a match in "develop"``.

But the SUMPRODUCT in your original posting used __two__ conditions for
selecting values in D2:D20 to sum. Namely:

=SUMPRODUCT(($B$2:$B$20=$E9)*($C$2:$C$20=F$1)*($D$2:$D$20))

The only concern you expressed was: "the text has to match exactly. Is
there a way to match partially (F$1)".

So I ass-u-me-d that you still wanted to compare B2:B20=E9, and it was only
the "exact match" of C2:C20=F1 that you wanted to change.

If I got that wrong, and if Fred's simplifying assumption does not work for
you, please explain exactly the conditions based upon which you want to sum
D2:D20.


----- original message -----

Not exactly sure what you mean. Perhaps:

=SUMPRODUCT(($B$2:$B$20=$E9)*ISNUMBER(FIND(F$1,$C$2:$C$20)),$D$2:$D$20)
Click to expand...

sorry I wasn't clear. I'm trying to do project management with Excel.
There are multiple tasks under the same category development; eg.
develop- graphics, develop- media, develop- audio. I would like them
to caculate the total time spent on develop. As long as there is a
match in "develop", it would be summed together. can I do that?

Thanks,



----- previous message -----

cpliu said:
=SUMPRODUCT(($B$2:$B$20=$E9)*($C$2:$C$20=F$1)*($D$2:$D$20))
In sumproduct above, the text has to match exactly.
Is there a way to match partially (F$1) such as containing
a word "develop" then it would be added together?
Click to expand...

Not exactly sure what you mean. Perhaps:

=SUMPRODUCT(($B$2:$B$20=$E9)*ISNUMBER(FIND(F$1,$C$2:$C$20)),$D$2:$D$20)

Note: See the Help pages for the difference between FIND and SEARCH. You
might prefer SEARCH, depending on your requirements.
 
Back
Top