Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumproduct critieria (Excel 2000)

    I need to make a certain string "ABX-050," that is part of a larger text string xxx-ABX050 xxxxxx, a criteria in a Sumproduct formula. I cannot include the whole text string because I need to have flexibility across company lines. I have a dynamic range name DESCRIPTION for the column that has this text. But I'm confused about how to speicify (DESCRIPTION "includes ABA050 in the string") as one of my arguments. I was thinking MID(This is what I don't know,4,7) to pull out that ABX-050 string to test, but how to say "every cell in the description range", is perplexing me. *(DESCRIPTION=MID(DESCRIPTION,5,6)="ABX-050")* is my latest failed attempt. Is this even possible? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumproduct critieria (Excel 2000)

    You can use ISNUMBER(SEARCH("ABX-050",DESCRIPTION)) in a sumproduct formula.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct critieria (Excel 2000)

    Thank you. The formula calculates to a number, however it is a different number, identified by a different text string. Its as if the ISNUMBER (SEARCH is ignoring the ABX-050 item. It is returning the total of the AFA0301 entry, a one-line entry. I tried ISNUMBER(SEARCH("ABX-050",DESCRIPTION,4)) but still get the same number. THe actual cell looks like this:996-ABX-050 J/E USD and the cells that are getting added up are 996-AFA0301 J/E USD. So I thought a start number of 4 would work, but it didn't. Thanks for any obsevations. I will keep playing around with it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumproduct critieria (Excel 2000)

    You asked about sumproduct criteria. Can you explain what you want to accomplish and what the whole formula is you have now?

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct critieria (Excel 2000)

    Here is the basic formula I am working on =SUMPRODUCT((BSNATURAL=B10)*(JE_NAME="Addition")*( BS_NET))+SUMPRODUCT((BSNATURAL=B10)*(JE_NAME="Adju stment")*(BS_NET))+SUMPRODUCT((BSNATURAL=B10)*(JE_ NAME="Purchase Invoices US")*(BS_NET)). It draws from a table of GL entries for a series of accounts listed in cells B10. the JE_NAME field has the terms you can see. Plus it has a terms like this: 9566-GHL050 J/E USD or 9566-ZFA0301 J/E USD. In a certain cell I need to do a formula such as above that gets me just the GHL050 amounts. BTW, BS_NET is the net debit or credit. What I have been asking is how to isolate and use that GHL050 string within the Description field so as to exclude the other stuff. As always, thanks for your ideas.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumproduct critieria (Excel 2000)

    Sorry, this leaves me even more in the dark than before. Perhaps you could attach a workbook with an indication of what you want.

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct critieria (Excel 2000)

    OK. Here is a file with test data in it. I have highlighted in yellow the cell with a SUMPRODUCT with the kind of issue I'm working on. THe "qualifying text" is part of a larger description field. You can see in the Detail, there are other entries that may be put elsewhere. So I need to find a way, in this particular cell, to get at only the CYL050 set of Descriptons. Thanks again for your assistance. I'm becoming quite a fan of SUMPRODUCT in that I have more format and placement control over large datasets than I do using a Pivot Table.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumproduct critieria (Excel 2000)

    The formula
    <code>
    =SUMPRODUCT((BSNATURAL=B9)*ISNUMBER(FIND("CYL050", JE_NAME))*BS_NET)
    </code>
    in cell E9 seems to do what you want. Am I correct?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumproduct critieria (Excel 2000)

    This seems to be a good opportunity to use a pivot table to get the summary info.

    You could even add a new column which contains this "description" (either add it manually or with a formula, or a combination of both) and use this description in the pivot table's "Page" field.

    While sumproduct seems to be less of a "memory/resource hog" than true "array formulas" (confirmed with ctrl-shift-enter) it still is an formula that works with arrays and so each of the sumproduct calculations is doing many manipulations (in your example each is doing thousands of manipulations). These can make calculations "sluggish" sinice a change or addition to any of the affected columns, requires every one of the formulas to be recalculated, since every one is affected by every row of data. The more data you add the more sluggish it will get.

    A pivot table only refreshes on command or if desired, you could have it on a new sheet and have it refreshed when the sheet is activated, so it is always "up-to-date" when you view it.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>,
    Steve

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sumproduct critieria (Excel 2000)

    Hans: Yes, sir! Works as needed. Thanks a million. As I said, this will come into play often, so the ability to express the condition in such a formula will be widely used.
    Re:Steve's comment: In fact, I have a titanic pivot table which develops all sorts of grouped subtotals. But it cannot be formatted into the existing setup, and you know how resistant to change (and with cause) folks can get. Changing the way something just "looks" can send people into a tailspin. So the sumproduct function fits nicely into the cells on the existing spreadsheet, does the job of grinding up the table into its constituent parts quite well, and allows the spreadsheet to look exactly as it does now, while saving staff considerable hand-crafting effort. Plus, these numbers feed other sheets, and I have found out the hard way that once a Pivot table recalculates, cells on other sheets referring to a cell "within" the table range can get a different number. So I'd rather not chance it. Thanks for your idea.

Posting Permissions

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