Hi,

I'm trying to find the appropriate formula for my needs. I need a formula
like SumIf, but the logical test only picks up bits of the text in a cell,
not the whole cell. e.g. If I wanted the sum of all transactions at PnP
store, I could enter a sumif formula and it would total all the corresponding
cells that had PnP in the corresponding cell even if there was other text in
the cell.

I hope that makes sense.

Simon

Using a structure like
=Sumproduct(--(CriteriaCheck),(NumbersToSum))

We can produce something like:
=SUMPRODUCT(--ISNUMBER(SEARCH("Find me",A2:A1000)),(B2:B1000))

Note that unless using XL 2007, you can't callout entire columns in
sumproduct. (but you can callout entire rows...go figure!)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SimonM" wrote:

Hi,

I'm trying to find the appropriate formula for my needs. I need a formula
like SumIf, but the logical test only picks up bits of the text in a cell,
not the whole cell. e.g. If I wanted the sum of all transactions at PnP
store, I could enter a sumif formula and it would total all the corresponding
cells that had PnP in the corresponding cell even if there was other text in
the cell.

I hope that makes sense.

Simon

Try it like this...

=SUMIF(A1:A10,"*PnP*",B1:B10)

Or, using a cell to hold the criteria:

D1 = PnP

=SUMIF(A1:A10,"*"&D1&"*",B1:B10)

--
Biff
Microsoft Excel MVP


"SimonM" wrote in message
...
Hi,

I'm trying to find the appropriate formula for my needs. I need a formula
like SumIf, but the logical test only picks up bits of the text in a cell,
not the whole cell. e.g. If I wanted the sum of all transactions at PnP
store, I could enter a sumif formula and it would total all the
corresponding
cells that had PnP in the corresponding cell even if there was other text
in
the cell.

I hope that makes sense.

Simon