Sumproduct with potentially multiple and dynamic or criteria

[Gunblade]

Hi guys,

I'm looking for any potential way to write this formula to be:
a) user friendly and/or
b) easier to manage

Currently, for example, I have a formula that goes like:

Please Login or Register  to view this content.

Now from time to time the number of criteria can change (Could have 2, Could have more than 10) and/or the codes might change (Instead of 1.1 it's 2.3 for example).

Is there anyway to manage this where if it needs changing I don't have to go into the formula to manually edit everything?

Thanks.

[shukla.ankur281190]

You have done hard coded comparison, which need to change every time manually according your need. Instead of hard coded you can give reference cell, in reference cell you can put criteria as drop down; if you change dynamically your result as per your selection.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Gunblade]

Don't notice any Go Advanced command or tab.
With regards to data, I'll have to take a look how to edit everything as some of this is confidentia

[Kasper(Spreadsheeto)]

The 'Go Advanced' tab is here:

\1

[Gunblade]

Ah right thought you meant on excel.

Example should be uploaded now.

Ex1 and Ex2 is the before and after, in the sense that different names have different criteria.

[Phuocam]

Try this in C5:

=IF(RIGHT(B5)="x",SUMPRODUCT(--(Data1!B$1:B$17=$H$2),--(LEFT(Data1!$C$1:$C$17,1)=LEFT(B5,1)),Data1!$D$1:$D$17),SUMPRODUCT(--(Data1!B$1:B$17=$H$2),--(ISNUMBER(SEARCH(Data1!$C$1:$C$17,B5))),Data1!$D$1:$D$17))

[shukla.ankur281190]

Try

C5

Please Login or Register  to view this content.

[jason.b75]

Assuming that examples like 2.1-2.3 should also include intermediate values such as 2.2 in the result.

Array formula to go into C5, then fill down.

This formula will interpret x as a wildcard, numbers separated by / as individual criteria, and numbers separated by - as start and end of a sequence.

=SUMPRODUCT(Data1!$D$2:$D$17*(Data1!B$2:B$17=$H$2)*IF(ISNUMBER(SEARCH("-",B5)),((--LEFT(Data1!$C$2:$C$17,3))>=(--LEFT(B5,3)))*((--LEFT(Data1!$C$2:$C$17,3))<=(--RIGHT(B5,3))),ISNUMBER(1/(SEARCH(MID(SUBSTITUTE(B5,".x","**"),(COLUMN($A$1:INDEX($1:$1,CEILING(LEN(B5),4)/4))-1)*4+1,3),Data1!$C$2:$C$17)=1))))  

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Gunblade]

Ah crap, sorry guys. Column B is misleading. While yes it is related to code, That section is supposed to be text only i.e. Names of code descriptors.
I've removed the names and just kept numbers for easier understanding.
You won't be able to use column B for formulas sorry

[FlameRetired]

Another way.

As shukla.ankur281190 has suggested rather that hard code the criteria set up the sheets so the criteria can be referenced. Those references can be done dynamically as the "codes" change.

This is all done in Ex1. It can be duplicated in Ex2.

Try parsing the "codes" that are in column B with this formula in G5:I8. These will be helper columns for dynamic referencing.

Please Login or Register  to view this content.

Then use this formula in C5:C8

Please Login or Register  to view this content.

Be sure to append "" to the parts Data1!$C$1:$C$17&"" as in the formula and the attached. Data1!$C$1:$C$17 are mixed numbers and text and need to match the text output of the parsed "codes". This also eliminates the need for calling the LEFT function.

[Gunblade]

Will take a look and try to understand the formula.
Not used them before so no idea what it means :P
But thanks for the example.
It's a little clunky if I need 10 codes in one line however but I guess short of designing and programming a software tool, this is the best I can do to make it as user friendly as possible for excel (Other people with little knowledge in excel with have to use the sheet also).

Thanks everyone for the input though, it's all much appreciated.
If anyone has any other shortcuts to add though please feel free to do so

[Gunblade]

After looking at that formula more closely I noticed we're still using column B for the data. In the real spreadsheet the codes may not be displayed as it'll be replaced with text descriptors.
We will still also have to manually change the formula in column C if we need to add more criteria.
Yes that's not difficult but Christ try telling that to my colleagues.
Essentially what I am looking for is.
1. Copy paste raw data into worksheet "Data1"
2. Change name H2 as necessary.
3. Done.
4. maybe if we need to change code criteria I type in some code numbers "1.1, 2.3, etc." in a separate cell and the formula in col C reads it and automatically changes. 1 criteria, 20 criteria, code 1.1 or code 1.x, it'll still work the same.

It doesn't have to be sumproduct, that's just what I know.
Though I'm not sure what else would work.

Am I being too overly optimistic and essentially what I'm asking for is, again, short of impossible except for a custom software program?

If it's not that simple they won't have a clue

[jason.b75]

Column B is misleading. While yes it is related to code, That section is supposed to be text only i.e. Names of code descriptors.

You will need to provide a new sample file that is not misleading if you still want an answer.

If you only have names of code descriptors in column B, then you will need to provide the information to compare the text to the raw data.

[FlameRetired]

I concur with what jason.b75 says above.

...............It's a little clunky if I need 10 codes in one line however but I guess short of designing and programming a software tool, this is the best I can do to make it as user friendly as possible for excel (Other people with little knowledge in excel with have to use the sheet also)...........

In the meantime, even with a new upload, some kind of parsing step might be necessary.

This does not have to be done with helper columns as in my previous upload. That parsing can be done with an array embedded in-formula. Even then it would be advantageous to have 1 helper column that calculates the number of "codes" criteria in each row of column B. That will be automatic and can be hidden.

It still makes for a lengthy array-entered formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. That may be confusing to colleagues. Also the one I have using the current data is about 2 lines long.

I overlooked something earlier.

It appears that some of the criteria have criteria as well. "Codes" criteria ending in ".x" are special cases.

I do not think we can help you further without a more representative (desensitized) example. Please feel free to include 10-20 "codes" criteria in some rows.

[Gunblade]

Will see what I can do, busy at work preparing for some business trips. May be today or sometime next week, thanks

[FlameRetired]

Looking forward to it.

[Gunblade]

Almost forgot about this.
Example 2 attached with comments in the spreadsheet.