Results 1 to 9 of 9

Thread: Sumproduct/if formula to ignore text string

  1. #1

    Sumproduct/if formula to ignore text string



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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.

  3. #3
    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.

  4. #4
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    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<>""),--('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 by CheshireCat; 2012-09-13 at 07:34 PM.

  5. #5
    Quote Originally Posted by CheshireCat View Post
    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<>""),--('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

  6. #6
    Quote Originally Posted by MZING81 View Post
    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?

  7. #7
    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)))

  8. #8
    Quote Originally Posted by Bob Phillips View Post
    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

  9. #9
    Why don't you post an example workbook.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •