# Thread: Sumproduct critieria (Excel 2000)

1. ## 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. ## Re: Sumproduct critieria (Excel 2000)

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

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