Hi Guys,
After having found more than I ever wanted to know about excel from this site over the past few years, I now find myself with a challenge I can't quite solve from previous posts. I've searched here -> http://www.excelforum.com/search.php?searchid=1529120 and not found my answer unfortunately so I hope some one has the time to help me.
I do hope that the title is clear enough.
Please see the attachmet for my sample data.
I need to populate the "New customer value" cell with a sum of all the values in colF where the customer is a new customer. New customer is defined as a customer who had no revenue, of any "type" for the previous month.
This array formula, derived from the help files example for counting unique values, gives me a count of the new OAC:
{=SUM(IF(FREQUENCY(IF(E2:E18=0,IF(F2:F18<>0,MATCH($B2:$B18,$B3:$B18,0),""),""), IF(E2:E18<0,IF(F2:F18<>0,MATCH($B2:$B18,$B2:$B18,0),""),""))>0,1))}
this is working correctly.
I have tried many variations on the following to attempt to sum the values, but it is incorrect:
{=SUM(IF(FREQUENCY(IF(E2:E18=0,IF(F2:F18<>0,MATCH($B2:$B18,$B2:$B18,0),""),""), IF(E2:E18<0,IF(F2:F18<>0,MATCH($B2:$B18,$B2:$B18,0),""),""))>0, INDEX(F2:F18,ROW(B2:B18))))}
I am struggling with referring to the current position within the array to determine which figure to add in the last section of the forumla.
I can manually achieve what I need for a given worksheet and month but I have 16 sheets spanning hundreds of rows across an 18 month period so automating will be the only way to get this done in a reasonable time frame.
I would be very grateful of any help you can give at all.
Many thanks in advance
FYI: I have also posted this question on ozgrid.com here http://www.ozgrid.com/forum/showthread.php?t=136960 but have not recieved a response as yet.
Bookmarks