• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Thinking outside the box???

tarynmahon

tarynmahon

Member
I have a formula

=IFERROR((SUM(IF(('H:BDMLMarketing CISMITarynSDLFiles to open[SDL Sales.xlsb]Apr12'!$N$12:$N$9236="ADON")*('H:BDMLMarketing CISMITarynSDLFiles to open[SDL Sales.xlsb]Apr12'!$AH$12:$AH$9236="Web")*('H:BDMLMarketing CISMITarynSDLFiles to open[SDL Sales.xlsb]Apr12'!$M$12:$M$9236="Inception"),1/COUNTIFS('H:BDMLMarketing CISMITarynSDLFiles to open[SDL Sales.xlsb]Apr12'!$N$12:$N$9236,"ADON",'H:BDMLMarketing CISMITarynSDLFiles to open[SDL Sales.xlsb]Apr12'!$M$12:$M$9236,"Inception",'H:BDMLMarketing CISMITarynSDLFiles to open[SDL Sales.xlsb]Apr12'!$AH$12:$AH$9236,"Web",'H:BDMLMarketing CISMITarynSDLFiles to open[SDL Sales.xlsb]Apr12'!$B$12:$B$9236,'H:BDMLMarketing CISMITarynSDLFiles to open[SDL Sales.xlsb]Apr12'!$B$12:$B$9236)),0))/E10,0)

It works brilliantly but unfortunately the people that are going to use my spreadsheet that has this formula in do not have as high a spec PC as I do and when they try to open it it crashes, there are lots of formulas in my spreadsheet but I have isolated the problem to be this particular formula, is there anything else I can do to get this working more efficiently? I also dont know if this is going to make any sense without an example but I am unable to upload anything due to company restrictions
 
=IFERROR((SUM(IF((Criteria1="ADON")*(Criteria2="Web")*(Criteria3="Inception"),1/COUNTIFS(Criteria1,"ADON",Criteria2,"Inception",Criteria3,"Web",Criteria4,Criteria4)),0))/E10,0)


Hopefully easier to understand?
 
Perhaps I should try to prep the source data rather than doing too much in the formula...

On that basis, is there anyway of working out the following;

COL A,COL B

CLIENT REF,PRODUCT

Client1,PC

Client1,ADON

Client1,ADON

Client2,PC

Client2,ADON

PC is a motor insurance policy, ADON is if there has been any extras added to the policy such as breakdown cover etc. there will be a new row for each ADON that the Customer has added.

Im trying to work out an ADON penetration rate but need it to only count 1 ADON, in other words, I dont care if a Customer has 10 ADONS all I need to work out is how many customers have taken optional cover of any sort
 
Looks like you need an average.

An easy way to speed up sumproduct is to create a new column that combines all your search criteria. In your case you would insert a column next to your data and concatenate your searches. E.g. =Adon&web&inception. This makes it so sumproduct only has to search for 1 criteria instead of 3.


Hope this helps. goodluck
 
I get what your saying but how do I get my formula to only return for one instance, eg.

my formula now is;

=SUM(IF('[SDL Sales.xlsb]Sep12'!$AN$12:$AN$11952="InceptionADONWeb",1/COUNTIFS('[SDL Sales.xlsb]Sep12'!$AN$12:$AN$11952,"InceptionADONWeb",'[SDL Sales.xlsb]Sep12'!$B$12:$B$11952,'[SDL Sales.xlsb]Sep12'!$B$12:$B$11952)),0)


This just returns 0
 
Hi, tarynmahon!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Thinking%20outside%20the%20box___%20%28for%20tarynmahon%20at%20chandoo.org%29.xlsx


It creates a unique client list in column D and counts ADDON in column E (being E1 the value searched for):

D2: =SI.ERROR(INDICE(A$2:A$6;COINCIDIR(0;CONTAR.SI(D$1:D1;A$2:A$6);0));"") -----> in english: =IFERROR(INDEX(A$2:A$6,MATCH(0,COUNTIF(D$1:D1,A$2:A$6),0)),"")

E2: =SI(D2="";"";CONTAR.SI.CONJUNTO(A$2:A$6;D2;B$2:B$6;E$1)) -----> in english: =IF(D2="","",COUNTIFS(A$2:A$6,D2,B$2:B$6,E$1))


D2 is an array formula so remember that should be entered with Ctrl-Shift-Enter instead of just Enter.


Hope it helps. Just advise if any issue.


Regards!
 
unfortunately I am unable to look at dropbox as my company restricts file sharing sites.

I have already done a simple =A1&B1&B2 formula to give me "InceptionADONWeb" in the source data, I now need another formula in a different workbook to look up this concatenated column and only give me a count of 1 per customer based on their customer reference number.


Please note I have a formula that works and gives me the result I need but I just needed to make it smaller so that the workbook calculated quicker, the answer above looks very complex to me, I just want to make sure it doesnt end up being more complex than my original formula
 
Basically Im trying to find out how many customers that bought car insurance policies with us also took out additional extras, such as Breakdown cover, Legal cover etc.

Unfortunately the source data that I have gives me rows and rows of data so for instance, 1 customer could have 5 rows of data, the first row being the car insurance policy and the other 4 for each extra they have added.

If I returned 4 extras per customer then I would have a penetration rate of 400% which I do not want to show therefore I only want to count one extra per customer.


Hope this helps to explain it
 
Also, my other formula is instant in returning a result it just makes the workbook too big to open for anyone using a lower spec PC than me
 
Hi, tarynmahon!


No need to access my uploaded file, just use the pasted formulas I posted.


D1: Unique Client List

E1: ADDON

D2:E2 : formulas posted


Copy down D2:E2 as required.


Regards!
 
Im sorry, I feel Im a bit out of my depth, I really dont know where to start.

Can you confirm whats in columns A & B please?


=IFERROR(INDEX(A$2:A$6,MATCH(0,COUNTIF(D$1:D1,A$2:A$6),0)),"")

=IF(D2="","",COUNTIFS(A$2:A$6,D2,B$2:B$6,E$1))
 
Can I really just not get this formula to use the concatenated reference instead of the 3 referrence it currently uses?

I feel this should be easy to just replace with one instead of three, no?


=SUM(IF((B4:B13="ADD-ON")*(C4:C13="Supplier D")*(D4:D13="Web"),1/COUNTIFS(B4:B13,"ADD-ON",C4:C13,"Supplier D",D4:D13,"Web",A4:A13,A4:A13)), 0)
 
Hi, tarynmahon!

In columns A:B I copied the data you posted earlier:

http://chandoo.org/forums/topic/thinking-outside-the-box#post-52401

I didn't checked your posted formula, I solved it with my D2 formula.

Regards!
 
I dont know if its my computer or not but I've just tried to copy the formula down in D2 and its crashed my machine, any other ideas please?
 
Hi tarynmahon,


* As you have huge range of data.. (11952 Rows)

* with multiple Criteria.. (Add-On, Supplier D, Web..)

* and you want just number of UNIQUE rows..


So I would like to re-point Montrey's suggestion..

An easy way to speed up sumproduct is to create a new column that combines all your search criteria. In your case you would insert a column next to your data and concatenate your searches. E.g. =Adon&web&inception. This makes it so sumproduct only has to search for 1 criteria instead of 3.
Click to expand...

Otherwise, it will took a long time for each calculation..


BTW you can use NAME Range.. to make the calculation little bit faster..


take a look at below post..

http://chandoo.org/forums/topic/wrapping-an-unique-distinct-value-array-formula-into-an-if-function#post-47464

Regards,

Deb
 
That's what I was going to go with but I cant work out how to include it within my formula as it returns a zero value and nobody could offer any advise other than SirJB7 with this other formula


Please can you help, my formula was;

=SUM(IF((B4:B13="ADD-ON")*(C4:C13="Supplier D")*(D4:D13="Web"),1/COUNTIFS(B4:B13,"ADD-ON",C4:C13,"Supplier D",D4:D13,"Web",A4:A13,A4:A13)), 0)


I now have another column in my source data that has concatenated the 3 fields, I would have expected the formula to now be;

=SUM(IF((E4:E13="InceptionADONWeb"),1/COUNTIFS(E4:E13,"InceptionADONWeb",A4:A13,A4:A13)), 0)

But as I say it does not work


Thanks in advance
 
Hi tarynmahon!


Just a silly question..

As I can see you are using the formula correctly, and you are modifying it also correctly.. So I hope you are using the formula from fist step, using CTRL + SHIFT + ENTER.. and if you are using with CTRL SHIFT ENTER, and still giving error, you may upload a sample file..

and if you not using any of the above formula with CTRL SHIFT ENTER not just ENTER.. please do so.. as your formula for fetching UNIQUE record is perfect...


Regards,

Deb
 
You must log in or register to reply here.
Back
Top