Count Partial Matches With Date Criteria

[colleenbaty]

Hello. I am attempting to count the number of instances of partial text matches for records that correspond with a specific month. For example, in the month of July 2015, I had eight accounts place 39 orders. I need a formula that will count the number of accounts active that month. I'm familiar with countifs and counting unique values, but the fact that I'm searching for partial matches seems to be causing a problem.

I have two formulas that I've been trying to combine, but I'm stymied. The first formula is to find LEFT partial matches and the second sets the date criteria. The formulas are:

=SUM(IF(FREQUENCY(MATCH("*"&LEFT(B2:B592,3)&"*",B2:B592,0),MATCH("*"&LEFT(B2:B592,3)&"*",B2:B592,0))>0,1))
=COUNTIF(G2:G592,"7/1/2015")

I'm not sure if I'm even on the right track there, but I thought I'd share so you can see where I'm at.

Thanks in advance for any tips you can share!

 

[Domenic]

For a unique count of partial matches in Column B, where the corresponding dates in Column G fall within the month of July in 2015, try...

=SUM(IF(FREQUENCY(IF(G2:G592-DAY(G2:G592)+1="7/1/2015"+0,IF(LEN(B2:B592)>0,MATCH(LEFT(B2:B592,3)&"*",B2:B592,0))),ROW(B2:B592)-ROW(B2)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[colleenbaty]

Hi, Domenic. Thank you so much for your assistance. It worked like a charm! I'm not completely clear on what this formula is doing, however, particularly because I'm working with months rather than days. If you have a moment to explain the part below that is bolded, I'd appreciate it. Thanks! Colleen

=SUM(IF(FREQUENCY(IF(G2:G592-DAY(G2:G592)+1="7/1/2015"+0,IF(LEN(B2:B592)>0,MATCH(LEFT(B2:B592,3)&"*",B2:B592,0))),ROW(B2:B592)-ROW(B2)+1)>0,1))

 

[Domenic]

G2:G592-DAY(G2:G592)+1

For each date in G2:G592, we take the date, then we subtract the day of the date, and then we add 1. The result will always be the first day of date's month. So let's say the date in G2 is 1/18/2016, here's how it would be evaluated...

1/18/2016 - DAY(1/18/2016) + 1

1/18/2016 - 18 + 1

1/1/2016

 

[colleenbaty]

I see. So if I'm looking for transactions that have dates other than the first of the month, would I use the MONTH function?

For each date in G2:G592, we take the date, then we subtract the day of the date, and then we add 1. The result will always be the first day of date's month. So let's say the date in G2 is 1/18/2016, here's how it would be evaluated...

1/18/2016 - DAY(1/18/2016) + 1

1/18/2016 - 18 + 1

1/1/2016

 

[Domenic]

The technique will pick up any date for that particular month/year because of the way it's structured. But maybe this will be easier for you to implement...

=SUM(IF(FREQUENCY(IF(G2:G592>="7/1/2015"+0,IF(G2:G592<="7/31/2015"+0,IF(LEN(B2:B592)>0,MATCH(LEFT(B2:B592,3)&"*",B2:B592,0)))),ROW(B2:B592)-ROW(B2)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

 

[Domenic]

And, if you're only interested in a single day, let's say July 20, 2015, we would use the following formula...

=SUM(IF(FREQUENCY(IF(G2:G592>="7/20/2015"+0,IF(LEN(B2:B592)>0,MATCH(LEFT(B2:B592,3)&"*",B2:B592,0))),ROW(B2:B592)-ROW(B2)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.