sumproduct with wildcards?

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hi,

will sumproduct() allow the use of wildcards? I'm trying to sum a portion of
a large spreadsheet. One column is numerical, the other is text. All of the
entries in the text column begin with common values but differ at the end,
hence my attempt at the use of a wildcard. But it doesn't seem to work. Any
suggestions?

thank you.

jim
 
No, SUMPRODUCT will not work (directly) with wildcards.

You can use something like this:

Sum values in column B where the values in column begin with xxx:

...........A..........B
1.....xxx12......10
2.....yyy55......20
3.....xxx00......10

=SUMPRODUCT(--(ISNUMBER(SEARCH("xxx",A1:A3))),B1:B3)

=SUMPRODUCT(--(LEFT(A1:A3,3)="xxx"),B1:B3)

Biff
 
Thanks again, Biff. I had played around with the LEFT function and actually
came up with the same thing you suggested, but the SEARCH feature is
perfect.

jim

T. Valko said:
No, SUMPRODUCT will not work (directly) with wildcards.

You can use something like this:

Sum values in column B where the values in column begin with xxx:

..........A..........B
1.....xxx12......10
2.....yyy55......20
3.....xxx00......10

=SUMPRODUCT(--(ISNUMBER(SEARCH("xxx",A1:A3))),B1:B3)

=SUMPRODUCT(--(LEFT(A1:A3,3)="xxx"),B1:B3)

Biff
Click to expand...
 
You're welcome. Thanks for the feedback!

Biff

Jim said:
Thanks again, Biff. I had played around with the LEFT function and
actually came up with the same thing you suggested, but the SEARCH feature
is perfect.

jim
Click to expand...
 
Back
Top