+ Reply to Thread
Results 1 to 9 of 9

sumproduct not calculating correctly

  1. #1
    Registered User
    Join Date
    06-10-2009
    Location
    AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    sumproduct not calculating correctly

    Hello, I am working on a project where I need to count how many times a certain phrase appears, while also fulfilling another criteria. Here is the formula I have been using that doesn't seem to be working.

    =SUMPRODUCT('05 11 09'!D2:D444="Cannot convert*")*('05 11 09'!G2:G444=1)

    It pulls the information off the sheet "05 11 09" and must match the phrase "Cannot convert*" in the D column where it begins with cannot convert and can have any ending. Then it also has to have the value of 1 in the G collumn. When I use this formula, it gives me 0, when it is more than 0. I have tried using the =sum and creating an array, but that hasn't worked either. I can use the countif function for just the first part (counting the cannot converts), but I need it to also match the second criteria.

    Any help on this is much appreciated.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: sumproduct not calculating correctly

    try in this format
    =SUMPRODUCT(--($G$2:$G$444=1),--ISNUMBER(SEARCH("cannot convert*",D2:D444)))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    06-10-2009
    Location
    AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: sumproduct not calculating correctly

    It works out great! I didn't realize the format was the problem

    I have one other question if you could help me. I now need to do a similar function where I have to count how many of the "Cannot convert*" appear, but do not count duplicates in relation to another column. What I mean is in column E (E2:E444) there are a list of invoices. I need to create a formula that calculates the amount of times "Cannot convert*" appears with a unique invoice. If "Cannot convert*" appears with an invoice more than once, then the duplicates shouldn't be counted. I am stumped on this and how to tackle it.

    Any help on this is again very much appreciated.
    Thanks!
    Last edited by icee; 06-10-2009 at 06:24 PM.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: sumproduct not calculating correctly

    not sure if you can do that!

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: sumproduct not calculating correctly

    Quote Originally Posted by martindwilson View Post
    try in this format
    =SUMPRODUCT(--($G$2:$G$444=1),--ISNUMBER(SEARCH("cannot convert*",D2:D444)))
    You don't really need the wildcard here, the formula works without it. If "cannot convert" is always at the start you could also use LEFT function like this:

    =SUMPRODUCT(--($G$2:$G$444=1),--(LEFT($D$2:$D$444,14)="cannot convert"))

    To count different invoice numbers in E2:E444 when D2:D44 is "cannot convert...... try this array formula

    =SUM(IF(FREQUENCY(IF(LEFT(D2:D444,14)="Cannot convert",IF(E2:E444<>"",MATCH(E2:E444,E2:E444,0))),ROW(E2:E444)-ROW(E2)+1),1))

    formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: sumproduct not calculating correctly

    i was about to suggest a helper column say F
    with in F2 dragged down
    =IF(COUNTIF($E$2:E2,E2)=1,1,0)
    then
    =SUMPRODUCT(--($F$2:$F$300=1),--($G$2:$G$300=1),--ISNUMBER(SEARCH("cannot convert*",D2:D300)))
    ps i agree on the wild card tho! it was a hangover from some other wild card search and i just put it in!

  7. #7
    Registered User
    Join Date
    06-10-2009
    Location
    AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: sumproduct not calculating correctly

    daddylonglegs,

    I used the formula and it seems to be working quite well! Although I understand the basics of what it does, I do not quite understand the workings of the formula. Is the LEFT function to create a substring to allow the extra characters after the "Cannot convert?" Could you explain how the formula works so I can use it as future reference?

    Thanks once again for the formula however!

    martindwilson,

    I originally thought of creating a helper column, but since this will be added to daily, I was trying to find a way to have a main workpage calculate the values without the use of adding columns. I believe your formula would work though.

    Also this may seem like a silly question but in the original sumproduct function that I used, do the "--" make it a true/false into a numeric value or something?

    Thanks again!

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: sumproduct not calculating correctly

    if you put
    =ISNUMBER(SEARCH("cannot convert*",D2)) it will return true/false
    =--ISNUMBER(SEARCH("cannot convert*",D2)) it will return 1/0
    its the 1/0 that the sumproduct uses to calculate

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: sumproduct not calculating correctly

    oops wrong post

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1