# Sumproduct/if formula to ignore text string

#### MZING81

##### New member
Hello Everyone,

I have a formula that I need to use in two different ways.
The first it needs to ignore the blank cells in a range, and sum two other ranges that meet a single text string criteria in each range. But one of the text strings is a combination of text with a dash and then a letter and a number I just need the first four characters. I tried the formula in a few different ways with no luck usually getting a "0".

=SUMPRODUCT('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1300="")*('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1300=D8)*('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1300=B8)

D8 is the text string that, four or more letter and then has a dash followed by a letter and number. The formula needs to ignore the "" in the range Q5:Q1300 then check the sum the other ranges.

this thread is not cross posted elsewhere.

MZING81

#### MZING81

##### New member
I've tried this one

=SUMPRODUCT(--('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1300="")*('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1300=D8)*('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1300=B8))

but still get a zero.

#### MZING81

##### New member
AND TRIED THIS ONE

=SUMPRODUCT(--('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1300="")--('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1300=D8)*('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1300=B8))

WHICH RETURNS, 508, BUT IT'S STILL OFF SHOUDL BE ABOUT 260ish.

#### CheshireCat

##### New member
Your formula appears to be counting only the occasions when there is a blank in the "Q" range, not excluding them. Try modifying like this:

Code:
``SUMPRODUCT(--('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1300[B][COLOR=#ff0000]<>[/COLOR][/B]""),--('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1300=D8),--('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1300=B8))``

Your description of the "first four Characters" is a little confusing, can you post a sample workbook to help clarify?

Last edited:

#### MZING81

##### New member
Your formula appears to be counting only the occasions when there is a blank in the "Q" range, not excluding them. Try modifying like this:

Code:
``SUMPRODUCT(--('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1300[B][COLOR=#ff0000]<>[/COLOR][/B]""),--('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1300=D8),--('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1300=B8))``

Your description of the "first four Characters" is a little confusing, can you post a sample workbook to help clarify?

Thanks for the help it almost completely worked.

=SUMPRODUCT(--('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1204<>""),--('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1204=D8)--('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1204=B8))

An example of B8 is listed in the spread sheet as HTTP-G5

#### MZING81

##### New member
Thanks for the help it almost completely worked.

=SUMPRODUCT(--('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1204<>""),--('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1204=D8)--('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1204=B8))

An example of B8 is listed in the spread sheet as HTTP-G5

To clarify the above statment there are a quite a few different forms of the text string beside HTTP-G5, but similar to that with text, some are longer but all roughly the same format.
Can I use a *, next to the cell reference?

#### Bob Phillips

##### Super Moderator
Staff member
Is this what you want

=SUMPRODUCT(--('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1204<>""),
--('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1204=D8),
--('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1204=LEFT(B8,FIND("-",B8)-1)))

#### MZING81

##### New member
Is this what you want

=SUMPRODUCT(--('SCHEDULE WEEK ONE'!\$Q\$5:\$Q\$1204<>""),
--('SCHEDULE WEEK ONE'!\$V\$5:\$V\$1204=D8),
--('SCHEDULE WEEK ONE'!\$U\$5:\$U\$1204=LEFT(B8,FIND("-",B8)-1)))

Thanks for the assistance! I tried it out but it didnt quite function, ended using a helper column. But still gotta work on how the first portion, column q, will be able to account for multiple text strings, maybe I can use, &.

MZING81

#### Bob Phillips

##### Super Moderator
Staff member
Why don't you post an example workbook.