COUNTIFS + dragging down

[JoshBright]

Hi there. I'm trying to get drag down to do the work for me and auto-populate what I need it to do.

I have a COUNTIFS function set up to count how many people in my database:
1. Own a specific product
2. Date of purchase for that product
3. Double opt in for marketing

As follows:

=COUNTIFS('Raw data'!AK:AK,"*Product1:01/01/2011*",'Raw data'!BQ:BQ,"Double")

Now I'd like to calculate how many people purchased that product on each day up until today.
I've tried doing this

A1 =COUNTIFS('Raw data'!AK:AK,"*Product1:01/01/2011*",'Raw data'!BQ:BQ,"Double")
A2 =COUNTIFS('Raw data'!AK:AK,"*Product1:02/01/2011*",'Raw data'!BQ:BQ,"Double")
A3 =COUNTIFS('Raw data'!AK:AK,"*Product1:03/01/2011*",'Raw data'!BQ:BQ,"Double")

And then dragging down but it just repeats those three dates.

Please note that Column AK:AK contains data for all products, comma separated, e.g:
Product1:01/01/2011,Product2:07/07/2017,Product4a:09/10/2016.

This is why it is searching that column for "*Product1:01/01/2011*" etc.

My desired end results is a table for Product1 showing
01/01/2011 4
02/01/2011 7
...
26/04/2018 1

Please help! I don't want to have to hand type all those dates!

Thanks

Josh

 

[wideboydixon]

Try this in A1:

=COUNTIFS('Raw data'!AK:AK,"*Product1:"&TEXT(DATE(2011,1,1)+ROW($A1)-1,"dd/mm/yyyy")&"*",'Raw data'!BQ:BQ,"Double")

WBD

 

[Marcelo Branco]

Say you have the dates in A1:An
01/01/2011
02/01/2011
...
26/04/2018

Then try in B1 copied down
=COUNTIFS('Raw data'!AK:AK,"*Product1:" &TEXT(A1,"dd/mm/yyyy")&"*",'Raw data'!BQ:BQ,"Double")

Hope this helps

M.

 

[JoshBright]

Doesn't seem to drag down and change the date to the next day?

 

[wideboydixon]

Doesn't seem to drag down and change the date to the next day?

Is that a response to my solution?

WBD

 

[JoshBright]

My initial response was to WBD.

Marcelo's solution seems to be working thus far. Got a couple of little tweaks to make and may come back to you with more newbie questions.

Thank you both of you for your time!!

 

[JoshBright]

Here comes another newbie question... already

Any idea why these counts wouldn't match up?

=COUNTIFS('Raw data'!AK:AK,"*Product1:*",'Raw data'!BQ:BQ,"Double") = 672

=COUNTIFS('Raw data'!AK:AK,"*Product1:" &TEXT(A1,"dd/mm/yyyy")&"*",'Raw data'!BQ:BQ,"Double") [added together for all dates 01/01/2011 til today] = 682

 

[JoshBright]

Here comes another newbie question... already

Any idea why these counts wouldn't match up?

=COUNTIFS('Raw data'!AK:AK,"*Product1:*",'Raw data'!BQ:BQ,"Double") = 672

=COUNTIFS('Raw data'!AK:AK,"*Product1:" &TEXT(A1,"dd/mm/yyyy")&"*",'Raw data'!BQ:BQ,"Double") [added together for all dates 01/01/2011 til today] = 682

A thought. Could this be because the first formula is counting how many people own product1, and the second formula may include 10 people who own 2 Product1's purchased on different dates?

 

[Marcelo Branco]

hmm...
Don't understand how the second formula can result in a greater number since it counts only for purchases in a specific date whilst the first formula counts for any date.
Weird...

M.

 

[JoshBright]

hmm...
Don't understand how the second formula can result in a greater number since it counts only for purchases in a specific date whilst the first formula counts for any date.
Weird...

M.

Exactly my thoughts. I wondered if it could count the same person (row) twice?

Probably not as my understanding was it counted rows where that particular criteria is met