Sumproduct/if formula to ignore text string

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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.

Thank you for your help

this thread is not cross posted elsewhere.

MZING81
 

MZING81

New member
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
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
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Mar 27, 2012
Messages
40
Reaction score
0
Points
0
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
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Why don't you post an example workbook.
 
Top