Need a formula to "weight" Marketing Results

[charville]

I am attempting to "weight" certain marking results and collect a total for the month. For example: Packet Sent = 3, Email Sent = 1, Emal Opened = 2, Phone Call = 3. I need help! I think I have created a mess. I know there has to be an easier way to do this. I have a sample attached below.

 

[Debraj]

Hi Carrie!

Welcome to the forum..
You can try this.. in L144..

=SUMPRODUCT(--(TEXT(L$3:L$140,"mmm-yy")=TEXT($K144,"mmm-yy")))

Drag to update in other cell as well..

PS: BTW, I have still not realised, why you have used *3 in your formula..

 

[Luke M]

Welcome to forum charville.

Please look at attached. I've gone through and cleaned up your COUNTIF type formulas. Many of them were incorrect, using strings instead of numbers (e.g. "1" instead of 1, "mar-13" instead of a real date).
Now that that's fixed, I think we can tackle the rating problem. However, the descriptors you listed are not found in your worksheet. Can you provide more info?

 

[NARAYANK991]

Hi Luke ,

A correction : COUNTIF does not distinguish between numeric and string criteria , and

=COUNTIF(numeric values range , 1)

will give the same result as

=COUNTIF(numeric values range , "1")

though the former should be used as a matter of good practice.

The problem with =COUNTIF(valid dates range , "mar-13") is not because the criterion is a string , but because Excel is not able to interpret the fragment as a valid date ; if it is slightly changed to :

=COUNTIF(valid dates range , "1-mar-13")

Excel will be able to interpret the criterion correctly , and will return the correct result.

Narayan

 

[charville]

Welcome to forum charville.

Please look at attached. I've gone through and cleaned up your COUNTIF type formulas. Many of them were incorrect, using strings instead of numbers (e.g. "1" instead of 1, "mar-13" instead of a real date).
Now that that's fixed, I think we can tackle the rating problem. However, the descriptors you listed are not found in your worksheet. Can you provide more info?

I cannot thank you enough for your help. What we are going for is creating a "score card" for marketing actions/reactions. So we are wanting to apply a score of "3" to packets sent, "2" for emails opened, "1" for emails sent, etc. I need to get a total "score" for say January 2013. So, I was trying/attempting to total per column per month per action, then need a total of all actions per month.

 

[Luke M]

Why do some of the columns have dates, and others just have numbers (e.g. col P and Q)? How do you categorize P and Q?

 

[Luke M]

Since it appears each of the groups you are talking about have their own column, it would seem that each could be assigned a multiplier. Is there some way/spot where different actions are listed in the same column?

 

[charville]

Since it appears each of the groups you are talking about have their own column, it would seem that each could be assigned a multiplier. Is there some way/spot where different actions are listed in the same column?

I really appreciate your response! I clearly have no idea what I am doing and probably need to rethink and restart. There will not be different actions in a column. The info/action will either be a date or a number per column.

 

[Luke M]

Ok. So, each column can have a multiplier based on what it is. This can be done by you using a row to define the multipliers, and then we'll use SUMPRODUCT to take a month's totals and the multipliers, and get overall total for month. We can get the month totals using the formulas already given. The part I'm confused on still, is what to do for columns with only a number entered? How should we be categorizing these into a month? Or would they get their own separate row?

Example of what I'm envisioning