SUMPRODUCT when cell contains letter

I am using a SUMPRODUCT to look through an expanding set of data on another sheet and based off the criteria sum the data.

When the criteria is a fixed number such as 921 or 889 it works great. SUMPRODUCT(--('GL3001'!C:C="921")) that works.

However I have a large section of data that the criteria begins with a letter. So it can be R5500 , R651000 , R31
I am trying to figure out how to include all the data that begins with that Letter.

I looked through the forum and tried the following;

Formula:

Please Login or Register  to view this content.

It returns a #Value, I believe because it isn't a number and is returning zero...

The rest of the arrays are working fine, just filtering that first one to include all data that begins with R.

Any guidance would be appreciated.

Thanks!

Post a SMALL sample file with about 20 rows worth of data and tell/show us what result you expect.

A SMALL file with about 20 rows worth of data will be about 10kb in size.

We only need to see data that is relevant to the problem.

This works for me:

=SUMIF(Sheet1!C:C,"R*",Sheet1!I:I)

Try this:
=SUMIFS(Sheet1!I:I,Sheet1!C:C,"R*",Sheet1!M:M,"ST")

Thanks! They both work great. And are a more elegant solution.

I do need the secondary check for ST.

I tried using the wildcard with Sumproduct, but I couldn't get it to work.

Would you mind explaining what the subtotal offset part of my original formula is doing?
If it isn't necessary, I think excel would run faster if I removed all the array formulas and replaced them with one of these.

Thanks again!

I can't really follow the SUBTOTAL part of your formula either. Maybe somebody else can help.

Array formulas, including SUMPRODUCT formulas, are slow, in my experience, when you use entire rows or columns. Try specifying a smaller range.

The SUMPRODUCT function doesn't accept wildcards.

The SUBTOTAL syntax is typically used on filtered data. You didn't have any filters applied in your sample file so I assume you don't need the SUBTOTAL syntax.

You're welcome!