NEED Excel FORMULA HELP ASAP! - Need Calculation for Cost per Member ID by Campaign Code and Sales Region


I really need help finding this out.  The final result I need to provide is:

1) Cost Per Campaign By Sales Region
2) Cost Per Member ID By Sales Region

I have to show a couple of views in a pivot:
Pivot 1
Sales Region,Count of unique Member ID,Unique Program Cost$ by Sales Region,Cost$ per unique program

I tried creating a formula in the Excel sheet but it did not work.

I left an example in the "Campaign" tab (shown in yellow).  I also provided a list of the unique campaign codes and unique costs in the tab "CAMPAIGN COST- Unique"

Relevant Fields:
Member ID
Sales Region
Campaign Code
Campaign Cost$
Pipeline $
Cost Per Campaign By Area
Cost Per Member ID By Area

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

No attachment...
GGLLExcelAuthor Commented:
Thanks for the heads up Polak.  The file is now attached.
I believe that I followed most of your question, I got a little confused where you started talking about pivots. Although, I think that the attached workbook should solve your question, just take a look at how I did the pivots and you should be able to follow along/modify accordingly.

If you need any of the data not in a pivot, just highlight the entire table and double click. Hope this helps!
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

GGLLExcelAuthor Commented:
Thanks for the effort, and it's very appreciated Polak, but this is not giving me any unique values by campaign code/by sales region with count of member ids (unique) to get cost per lead.
Perhaps I'm misunderstanding, in either case I'm turning in for the night, I'll defer to another expert given your time crunch.
Saurabh Singh TeotiaCommented:
Okay so if i understand your question correctly you want to populate Column-H and Column I for the sheet tabs Campaign?

Now for Column-H you want to divide the campaign cost with unique regions we had and want to show in their so for instance this campaign Campaign Code  1234858 has 4 unique reasons you want to divide $3,200,000/4 = $800,000 in each region which you want to show only once per region..Is my understanding correct?

Similarly For Column-I you want to look at unique member id in column-A and divide by that for the same campaign it will be 3200000/5 in case of member id..Is my understanding correct?

GGLLExcelAuthor Commented:
Yes, Saurabh, that is perfect understanding of the issue!
Saurabh Singh TeotiaCommented:
Their you go..Wrote a formula for you which does the same thing..Note..i have wrote this formula till row-30..You can drag it till your end point..But remember it might take some time to calculate but it will give the results what you are looking for..

You can test the same with the answer where formula is their before dragging it.. And again it's an array formula which need to be entered by ctrl+shift+enter..


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GGLLExcelAuthor Commented:
THANKS!!!!! It's super slow but it works perfectly!!!!!!!!!!!!!!!!!!!  You are a life saver!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.