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

 

[Marcelo Branco]

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

Could you post a small data sample (~10 rows) where such strange results occur?

M.

 

[JoshBright]

Not really, seeing as I don't know where the strange results are.

I have an existing table that counts the Number of people who own each product, e.g:
Product1 672 =COUNTIFS('Raw data'!AK:AK,"*Product1:*",'Raw data'!BQ:BQ,"Double")
Product2 543 =COUNTIFS('Raw data'!AK:AK,"*Product2:*",'Raw data'!BQ:BQ,"Double")
Product3 180 =COUNTIFS('Raw data'!AK:AK,"*Product3:*",'Raw data'!BQ:BQ,"Double")

I then have my new table for Product1 Purchase dates, which goes along the lines of:
01/01/2011 5 =COUNTIFS('Raw data'!AK:AK,"*Product1:" &TEXT(A1,"dd/mm/yyyy")&"*",'Raw data'!BQ:BQ,"Double")
01/02/2011 3 =COUNTIFS('Raw data'!AK:AK,"*Product1:" &TEXT(A2,"dd/mm/yyyy")&"*",'Raw data'!BQ:BQ,"Double")
...
26/04/2018 2 =COUNTIFS('Raw data'!AK:AK,"*Product1:" &TEXT(A1234,"dd/mm/yyyy")&"*",'Raw data'!BQ:BQ,"Double")
TOTAL = 682



​Really weird, like I said my only though is that it could be counting people who own multiple Product1's multiple times? Because of the differing dates of Purchase?

 

[Marcelo Branco]

I think i understand what is going on.

Records (rows) that contain something like (for example)
Product1:02/01/2011,Product1:07/07/2017,Product4a:09/10/2016

are counted twice - one for 02/01/2011 and another for 07/07/2017

M.