count account numbers that appear in 2 lists and sum values in different column

[glaupie]

Hi,

I have a list of account numbers (about 40,000) of people I sent a marketing text message to.

Now, using a transaction extract (showing details like acct # and values) I want to determine which of the people I contacted made a purchase since the message, and the total turnover generated by them.

I've played around with conditional formatting, pasting the list of contacts' account #'s below the list of unique account #'s of those who has made a purchase, then I higlight the duplicates. But that is as far as I get - my computer is VERY slow when using conditional formatting on long lists, so I would prefer a different way of doing this. But if that is the best way, I need to determine the amount of people highlighted in the "purchase" list. almost like a COUNTA(conditionally formatted) formula.

then I need to sum the value of their purchases, which is hard because some people bought more than once.

Any ideas?

So basically I need to count and sum the purchases of anyone who's account number matches any account number in a different list. (Another problem is that some acct #'s are 20 characters long and excel seems to only look at the first 16 digits when looking for duplicates - but that bridge I will cross later.)

 

[glaupie]

I should have thought thsi through - vLookup takes care of most of my problem.

I now know how many of the contacted account holders transacted - but vLookup only returns the 1st value if an account number appears twice. Any way to get around that?

Thanks,

 

[Tigs81]

glaupie

I would use a Sumif formula in one column, then use Countif formula in another column
then just total them to get your end result.

Thanks
tigs

 

[glaupie]

Tigs,

I'm afraid I don't follow...

I now have the list of people I contatced, and next to them the amount of their FIRST purchase, or #N/A.

How do I get the total turnover, that includes their 2nd and 3rd purchases?

 

[Tigs81]

Sorry wasnt thinking properly. Dont know why i said you need to total them.

B Column is the amount Column

=SUMIF(B1:B1000,">0")

=COUNTIF(B1:B1000,">0")

I assume that you only need a total for the whole marketing list not for each individual account.