If and Sum ... possibly...!

Hi guys,

Thanks for looking.

I have two questions with regards to the attached workbook and was wondering if someone could take a look and advise. The WS of interest is Final

My first question is in regards to the formulae which I have used in I2:M2. They work... but are they the best option? I feel that there may be a better way.... there usually is!

Context

Each row is a customer record. What I am trying to achieve is one row which shows all customer interaction. The ws' Paywall, Leads Website, Newsletter, Membership are all different types of interactions. The formulae are pulling these interactions where appropriate.

My second question, where i'm really quite stuck!

I am trying to create a formula in N2 which calculates a score based on the values in H2:M2 against a weighting table Q1:S7 (points in S2:S7 to be awarded)

I have manually entered the score for the first 10 rows.

Many thanks for your time

Oh Gosh, thanks! I wasn't thinking .. thanks for that!

Hi,

Can you explain how your weightings are calculated? e.g. in N7 how do you get the value 2, when J, K & L result in a total of 4?

Hi Richard!! doh!!! i hid the col Q, R and S!!!

Thanks for that!! hope all well with you!!

I'll delete the file,,,, again and reload!!

Hi,

I understand where the data comes from and had already unhidden your weighting table. As I say what I don't understand is the arithmetic you are .using to get your column N values. Would you explain the calcs

Hi Richard, thanks for your response and sorry for the delay in getting back to you.

Calculations as follows

H2 - Value in H2*S2
I2 - if it contains a date 2 points are awarded (S3) if I2 is blank then no points
J2 - same as above (s4)
K2 - if cell contains "Daily Newsletter" then 1 point is awarded (S5)
L2 - if cell contains "Daily Newsletter" then 1 point is awarded (S6)
M2 - if cell contains "Would you like to receive Utility Week Magazine? Tick this box to receive a special offer £25 discount" 2 points are awarded (s7)

As per my upload where i manually wrote the score N2 calculation = H2*S3 as I2:M2 are blank

in N3 the score is 2 as per s7

Does this make sense?

Thanks Richard