COUNTIFS - dragging formula across cells while adding to the range

[aadeli]

Essentially, I am trying to count the number of "first time customers" every month. I have a list of customers and for every month, it lists how much they ordered. There will be a "0" up until their first month they order, when it will then have a value greater than 1. So in my list of customers, I want to count the number of customers that have ordered a case in that month, but never in a previous month. This means that I have the following formulas:

June 2015 (appearing in column C): =COUNTIFS(C22:C1002, ">0",A22:A1002,"Customer")
July 2015 (appearing in column D): =COUNTIFS(D22:D1002, ">0", C22:C1002, "=0",A22:A1002,"Customer")
August 2015 (appearing in column E): =COUNTIFS(E22:E1002, ">0", D22:D1002, "=0", C22:C1002, "=0", A22:A1002,"Customer")

I have to manually add in the additional conditions for each month - I can drag the formula across, but it does not aggregate the columns, it only keeps the set size for a different time frame. I've tried doing the following, which also did not work:

August 2015 (appearing in column E): =COUNTIFS(C22:E1002, ">0", A22:A1002, "Customer").

Any help would be greatly appreciated!!

 

[sheetspread]

Are you putting in $ signs either manually or with the F4 key? The ranges will change otherwise as you drag/copy.


You don't need "=0", just 0

 

[aadeli]

I'm not trying to just lock the cell range - I want to keep the cell range but also add to it as I go...does this make sense?

So using my above example,

June 2015 (column C - the first date that I'm counting and have data for): I just want to count the values in column C - there is no previous orders so anyone ordering in this month is by default a first time customer.

July 2015 (appearing in column D - the second month that the product could be purchased): I want to count column D only if the corresponding cell in column C is 0. If there is a value in column C, then column D is a recurring, not first time order.

August 2015 (column E): I want this to count in column E if C and D are 0

Name June 15 July 15 August 15
Company A 1 1 0
Company B 0 0 3
Company C 0 1 1
Company D 1 0 1

So First time customers in June is 2, first time customers in July is 1, and August is also 1.

Hopefully this helps clear things up!

 

[sheetspread]

It's easier if you unpivot the data with this method:

https://www.youtube.com/watch?v=xmqTN0X-AgY

Then:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Month		New customers				Name	Date	Value
2	Jun-15	2	2				Company A	Jun-15	1
3	Jul-15	3	1				Company A	Jul-15	1
4	Aug-15	4	1				Company A	Aug-15	0
5							Company B	Jun-15	0
6							Company B	Jul-15	0
7							Company B	Aug-15	3
8							Company C	Jun-15	0
9							Company C	Jul-15	1
10							Company C	Aug-15	1
11							Company D	Jun-15	1
12							Company D	Jul-15	0
13							Company D	Aug-15	1
Sheet1 (3)

Cell Formulas
Range	Formula
C2	=B2-B1
B2	{=SUM(--(FREQUENCY(IF($H$2:$H$13<=A2,IF($I$2:$I$13<>0,MATCH($G$2:$G$13,$G$2:$G$13,0))),ROW($G$2:$G$13)-ROW(G2)+1)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.