Compair 2 sets of numbers

**Dryclean** · 03-02-2010, 12:35 PM

I have a business in which I compare my customer scores monthly
Attached is a sheet

Column A is Cust ID and B is Cust score previous moth

Column D is Cust ID and E is Cust Score current moth

Column E is the Movement in score If found

=(E3-(LOOKUP(D3,A:A,(B:B))))

This formula works but if the current customer is not found in Column A
It uses the score in column A that is just under it.
For example say 793 is in the D column current list but not found in column A
But 795 is in column A it will use 795 Score in the computation which is wrong

This formula works in finding customers but the computation doesn’t work

=IF((SUMIF(A:A,D2,B:B))>0,(E2-(SUMIF(A:A,D2,B:B))),0)

Also not sure what I can do if current customer not found maybe put a NULL for score

Help would be appreciated

**DonkeyOte** · 03-02-2010, 12:41 PM

The values in Columns B & E are numbers stored as text - ie they are not numbers per se... the SUMIF will always return 0.

If need coerce the values in B & E by entering the number 1 into a blank cell, copying it - highlighting the values in B & E and running Paste Special: Multiply.

You should then find:

=IF(COUNTIF(A:A,D4),E4-SUMIF(A:A,D4,B:B),0)

**Dryclean** · 03-02-2010, 01:25 PM

I feel so dumb don't notice not a number

It worked thanks

But what can I do if cust id in column d not found in a right now it puts a 0 for movement I want maybe it to say NULL . . . can that be done

what it means is I have my total customer list from the day I opened but I only consider them active if been in in the last 365 so someone can be current and not in the previous period because the last period they were over a year and now they came back "which is a GOOD thing