Countifs formula return wrong values

[glaupie]

Hi,

I am using the Countifs function to count the number of times a customer (using their account number in the formula) has made a transaction in any given week. My formula looks like this (for purchases):

=COUNTIFS('15-Aug-2010'!$C$3:$C$9072,'SMS Tracking'!$C4,'15-Aug-2010'!$M$3:$M$9072,"Store Purchase")+COUNTIFS('15-Aug-2010'!$C$3:$C$9072,'SMS Tracking'!$C4,'15-Aug-2010'!$M$3:$M$9072,"Tenacity Purchase")

It works fine for the most part, but I noticed that some are returning a value way too big, like 38. It seems the problem lies with the account numbers that are very long. I have my account numbers in Column C, and all of these that return the wrong values, are 20 digits in length. It seems the 38 that the formula finds, are 38 account numbers that start with the same 17 digits, but the last 3 are different. I've tried incorporating a LEFT formula in there, but some of the account numbers are less than 17 digits.
The account numbers are stored as text.

The really weird thing is that if this is the case there should be more errors, because there are more account numbers that are 20 long and start with the same 17 digits. I am copying this formula down 50 000 rows in the 'SMS Tracking' sheet, it takes FOREVER to do the calculation, the first couple of thousands are fine, then the errors start later on down the sheet.

It's driving me crazy, and ideas?

Any ideas?

 

[Richard Schollar]

It's a problem with COUNTIF and COUNTIFS - any strings of digits (even if entered in text format) over 15 digits produce this. Fortunately, you can use a SUMPRODUCT instead:

=SUMPRODUCT(--('15-Aug-2010'!$C$3:$C$9072='SMS Tracking'!$C4),('15-Aug-2010'!$M$3:$M$9072="Store Purchase")+('15-Aug-2010'!$M$3:$M$9072="Tenacity Purchase"))

This will be a slow calc if you are doing this for 50,000 lines.

 

[glaupie]

That seems to work like a charm, much appreciated. I've only tested in on a fewm they seem correct, and I'm not waiting for the calculation to finish. It's slow, but I can dealm with that.

Do you know how I can now add the values of these purchases? So, where it says "Store Purchase in Column M, I have an amount in Column N.

Thanks!

 

[Richard Schollar]

Sure - you just need to add an additional term into the formula:

=SUMPRODUCT(--('15-Aug-2010'!$C$3:$C$9072='SMS Tracking'!$C4),('15-Aug-2010'!$M$3:$M$9072="Store Purchase")+('15-Aug-2010'!$M$3:$M$9072="Tenacity Purchase"),'15-Aug-2010'!$N$3:$N$9072)