need help in sum up multi criterias (index & match)
Please find the screenshot in attached file.
I have 4 customer in the list. Each customer can have more than 1 security number.
as per example in the file, for customer 2, security number are same. so i will look at the security type, if the security type is different, then i will sum up both security amount. if the security type is same, then i will take the highest security amount. if the security amount is same, i will oni take 1 amount.
how should i write the formula? i have 3000+ lines to go. can i use index & match function?
It depends what you want really.
The fastest way to generate the information is inserting a pivot table. Drop Cust into the left hand pane and SecAmt into the main pane. Every time you want to refresh the data click the "!" on the pivot table toolbar.
The other way if you want to stay closer to your current methodology is to use the SUMIF function. See graphic for example.
Originally Posted by MartinShort
but then i dun want sum up everything, i want to check if security no. for tat customer is repeating, thne i will check whether the type is same o different. if same, i will oni take 1 security amount.
No idea, but perhaps the attached might trigger some brighter person to a result.
Originally Posted by aroundyou
I presume that your data is in Customer order (as shown in your .jpg) and that you want to pick up unique security amounts as shown shaded in column I (matches your indicated selections in column D)
I have not yet worked out how to omit the yellow-flagged 184 (184,000) nor how to make the 'Indirect' dynamic within the sumproduct, I could only achieve that with 3 helper columns.
You may just have to set this to VB code to achieve all that you asked.
added, in column J, in J2 put:
this will highlight the duplicates prior to the last entry (ie, the first of 2, the first 2 of 3 etc), but the Indirect ranges would need to be B-Start:B-Last etc, not B-Row-:B-last for the customer to include all duplicate entries.
Last edited by Bryan Hessey; 10-15-2006 at 08:05 PM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1