sumproduct with wildcards?

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
 
T

T. Valko

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
 
J

Jim

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...
 
T

T. Valko

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...
 
Top