Countif NOT working, returning 0...

[91709jack]

Hi all,

Here's the formula that's not working...

=COUNTIF(AK52:AK35533,CONCATENATE(T$4,U$4,"??16??m1"))

Stumped!

THANKS for your help!

Jack

 

[Aladin Akyurek]

Wat is m1 - just a string or the cell M1?

 

[91709jack]

Wat is m1 - just a string or the cell M1?

Hi Aladin - THANKS by the way - you were the one who helped me with the ACONCAT function some time back.

Sorry for not clarifying - dangers of posting at way past bedtime.

=COUNTIF(AK52:AK35533,CONCATENATE(T$4,U$4,"??16??m1"))

AK52:AK35533 - the range of cells, long strings, client info basically

T$4 & U$4 - hard locations, string values, year and month in 2 character format respectively.

"??16??m1" - the way I've logged client visits over the years. The whole string looks like YYMMDDhhmmpW (YY- year, MM - month, DD - day, hh - hour in 24 hr format, mm - minutes, p - who I met, m for the husband, f for thewife, etc; W - weekday in 1-digit format, eg 1= monday)

So currently, CONCATENATE(T$4,U$4,"??16??m1") will come up with "1711??16??m1" - basically counting the number of occurrences in the month of November, day 11, 16= 4pm, m - spoke with the guy, 1 - monday)

Actually stayed up till 4am and found a way to do it using the SEARCH function mainly with the wildcards. Love to see your take on it.

THANK YOU!

 

[Aladin Akyurek]

COUNTIF should work too. As you say, SEARCH will certainly work:

=SUMPRODUCT(ISNUMBER(SEARCH(T$4&U$4&"??16??m1",AK52:AK35533))+0)

Care to provide a string from AK, a value from T4, and a from U4 for testing, where COUNTIF fails?
<strike></strike>

 

[91709jack]

COUNTIF should work too. As you say, SEARCH will certainly work:

=SUMPRODUCT(ISNUMBER(SEARCH(T$4&U$4&"??16??m1",AK52:AK35533))+0)

Care to provide a string from AK, a value from T4, and a from U4 for testing, where COUNTIF fails?
<strike></strike>

Every COUNTIF returns a 0 when I know it's not the case.

That said, your formula worked, but now every time I enter and update the info in AK, it goes from about 16 seconds to almost 45 seconds. Is there a different way of writing that formula work a little faster? FYI, my spreadsheet is about 35k rows by 150 columns... thanks!

 

[Aladin Akyurek]

Every COUNTIF returns a 0 when I know it's not the case.

That said, your formula worked, but now every time I enter and update the info in AK, it goes from about 16 seconds to almost 45 seconds. Is there a different way of writing that formula work a little faster? FYI, my spreadsheet is about 35k rows by 150 columns... thanks!

Does this work?

=COUNTIF($AK$52:$AK$35533,"*"&T$4&U$4&"??16??m1"&"*")

 

[91709jack]

THANK YOU! It did work. Out of curiosity, here's how I had done it, compared to yours which worked: what's the difference?

Me: =COUNTIF(AK52:AK35533,CONCATENATE(T$4,U$4,"??16??m1"))

Yours: =COUNTIF($AK$52:$AK$35533,"*"&T$4&U$4&"??16??m1"&"*")

 

[Aladin Akyurek]

THANK YOU! It did work.

You are welcome.

Out of curiosity, here's how I had done it, compared to yours which worked: what's the difference?

Me: =COUNTIF(AK52:AK35533,CONCATENATE(T$4,U$4,"??16??m1"))

Yours: =COUNTIF($AK$52:$AK$35533,"*"&T$4&U$4&"??16??m1"&"*")

Since SEARCH appeared to work and the first COUNTIF formula above did not, it became obvious that we needed partial matching.
The first COUNTIF formula does in fact full matching, while the second COUNTIF does partial matching, that is,
when T$4&U$4&"??16??m1" is just a part of an AK cell, it counts that cell in.
Recall that a pair of * and * round the the search subtring means "contains".

 

[91709jack]

[Recall that a pair of * and * round the the search subtring means "contains".]

OHHHHH! The thing is I only know the basics of excel. I've learned the hairier aspects and techniques by googling all over...

Till my next pickle. THANK YOU AGAIN!

 

[91709jack]

But wait!

Say an AK cell contained TWO, or even 27 instances of substring "1?????1???m?" - will COUNTIF still work?