We help IT Professionals succeed at work.

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

on
Hi.

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

Comment
Watch Question

## View Solution Only

Commented:
No attachment...

Commented:
Thanks for the heads up Polak.  The file is now attached.
Campaign---Cost.xlsx

Commented:
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!
Campaign---Cost-Polak.xlsx

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.

Commented:
Perhaps I'm misunderstanding, in either case I'm turning in for the night, I'll defer to another expert given your time crunch.
Top Expert 2015

Commented:
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?

Saurabh...

Commented:
Yes, Saurabh, that is perfect understanding of the issue!
Top Expert 2015
Commented:
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..

Saurabh...
Campaign---Cost-1.xlsx

Commented:
THANKS!!!!! It's super slow but it works perfectly!!!!!!!!!!!!!!!!!!!  You are a life saver!