Count instances in a column where a value exists for first time in a row.

[andrews1vt]

I have a massive spreadsheet with several thousand unique customer IDs as the rows, and months as the columns. Monthly sales for each customer are the values (If Column A are the customer IDS and row 2 is customer X, and Column B is the month of January, then cell b2 would be the january sales for customer X.) For a specific month, I want to count the number of customers who are new. In other words, for that specific column, I want to count the instances where a value exists for the first time for that customer row. This can be accomplished using something like =countifs(Column E,">"&0, Column D, "",Column C,"", Column B, "") where I'm counting all the instances where a positive value exists and all preceding columns are blank. This becomes increasingly difficult when more months are involved (say for example, I want to count new customers for the month in column AZ, there would be 50+ criteria to add to the formula.) Is there a better or more efficient formula that will accomplish this?

To make things even even more confusing, I also want to do this using additional conditions. Each customer is labeled as either Criteria A or Criteria B, and I want to count the new customers with Criteria A separate from the new customers with Criteria B. Using the formula above, its simply an additional condition to the =countifs function.

Thanks for your help.
-Frustrated Controller.

 

[Russell Hauf]

Dear Frustrated ,

Instead of making this as confusing as possible, would you mind showing what you're ultimately trying to get at? Do you want to show first-time users by month? Amount of first-time spend by month? Something else?

 

[andrews1vt]

Thanks for the response. I'm trying to show the count of new customers by month as well as the sum of new revenue per month. I've added a simplified chart below to illustrate (didn't know this existed in my first post!) My actual spreadsheet has 60 months and 10,000+ customers.

In January, there 2 new customers paying $10 each, or $20 in total new revenue. In February, 1 new customer and $10 new revenue. March/April, no new customers and therefore no new revenue (customer 3 not counted as new since he's paid in a previous month.) Nothing in May, and then 1 new customer and $10 new revenue in June.

Customers	Jan	Feb	Mar	Apr	May	June
Customer 1	10	10	10	10	10	10
Customer 2	10	10	10	10	10	10
Customer 3		10		10	10
Customer 4						10
New Customers	2	1	0	0	0	1
New Revenue	20	10	0	0	0	10

<tbody>
</tbody>

Hopefully this helps! The goal is not to have to recreate the table or add any additional fields.....

 

[Russell Hauf]

Ok, how about something like this. I have kept your Customers sheet...but could you add a sheet, or use another workbook for your calculations / chart?
So here's the CUSTOMERS sheet - I've formatted my dates (which are just the first day of each month for a few years) as YYYYMM:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	CUSTOMER	201401	201402	201403	201404	201405	201406	201407	201408	201409	201410	201411	201412	201501	201502	201503	201504	201505	201506	201507	201508	201509	201510	201511	201512	201601	201602	201603	201604	201605	201606	201607	201608	201609	201610	201611	201612
2	1	0	0	0	0	0	0	10	6	6	8	4	9	40	40	50	30	40	50	50	50	40	50	0	40	10	30	30	10	0	50	50	20	50	10	0	20
3	2	0	4	9	7	10	3	1	8	0	0	5	8	30	0	0	10	20	50	50	30	50	50	40	0	40	30	40	50	10	40	0	50	40	0	40	40
4	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	20	0	0	20	20	50	50	10	20	0	30	10	50	10	0	40	50	0	50	0	40
5	4	0	0	7	2	8	5	3	5	5	8	9	3	30	40	30	10	40	40	0	10	50	20	0	40	20	10	50	0	0	0	40	30	40	20	10	30
6	5	0	8	3	9	4	5	6	7	2	1	4	4	30	20	50	10	40	50	50	50	50	0	20	0	10	50	10	30	50	30	30	0	0	20	0	0
7	6	0	6	9	7	9	7	2	5	3	10	8	3	50	20	10	30	0	10	10	50	40	50	20	50	10	10	40	0	50	50	0	20	40	40	50	30
CUSTOMERS

Then on another sheet, I have the calculations:

Excel 2012
	A	B	C
1	CUSTOMER	FIRST MONTH	FIRST MONTH REVENUE
2	1	201407	10
3	2	201402	4
4	3	201504	20
5	4	201403	7
6	5	201402	8
7	6	201402	6
CALCS

Cell Formulas
Range	Formula
A1	=CUSTOMERS!A1
A2	=CUSTOMERS!A2
A3	=CUSTOMERS!A3
A4	=CUSTOMERS!A4
A5	=CUSTOMERS!A5
A6	=CUSTOMERS!A6
A7	=CUSTOMERS!A7
B2	{=INDEX(CUSTOMERS!$B$1:$AK$1,MIN(IF(CUSTOMERS!$B2:$AK2>0,COLUMN(CUSTOMERS!$B2:$AK2)-1,"")))}
B3	{=INDEX(CUSTOMERS!$B$1:$AK$1,MIN(IF(CUSTOMERS!$B3:$AK3>0,COLUMN(CUSTOMERS!$B3:$AK3)-1,"")))}
B4	{=INDEX(CUSTOMERS!$B$1:$AK$1,MIN(IF(CUSTOMERS!$B4:$AK4>0,COLUMN(CUSTOMERS!$B4:$AK4)-1,"")))}
B5	{=INDEX(CUSTOMERS!$B$1:$AK$1,MIN(IF(CUSTOMERS!$B5:$AK5>0,COLUMN(CUSTOMERS!$B5:$AK5)-1,"")))}
B6	{=INDEX(CUSTOMERS!$B$1:$AK$1,MIN(IF(CUSTOMERS!$B6:$AK6>0,COLUMN(CUSTOMERS!$B6:$AK6)-1,"")))}
B7	{=INDEX(CUSTOMERS!$B$1:$AK$1,MIN(IF(CUSTOMERS!$B7:$AK7>0,COLUMN(CUSTOMERS!$B7:$AK7)-1,"")))}
C2	{=INDEX(CUSTOMERS!$B2:$AK2,MIN(IF(CUSTOMERS!$B2:$AK2>0,COLUMN(CUSTOMERS!$B2:$AK2)-1,"")))}
C3	{=INDEX(CUSTOMERS!$B3:$AK3,MIN(IF(CUSTOMERS!$B3:$AK3>0,COLUMN(CUSTOMERS!$B3:$AK3)-1,"")))}
C4	{=INDEX(CUSTOMERS!$B4:$AK4,MIN(IF(CUSTOMERS!$B4:$AK4>0,COLUMN(CUSTOMERS!$B4:$AK4)-1,"")))}
C5	{=INDEX(CUSTOMERS!$B5:$AK5,MIN(IF(CUSTOMERS!$B5:$AK5>0,COLUMN(CUSTOMERS!$B5:$AK5)-1,"")))}
C6	{=INDEX(CUSTOMERS!$B6:$AK6,MIN(IF(CUSTOMERS!$B6:$AK6>0,COLUMN(CUSTOMERS!$B6:$AK6)-1,"")))}
C7	{=INDEX(CUSTOMERS!$B7:$AK7,MIN(IF(CUSTOMERS!$B7:$AK7>0,COLUMN(CUSTOMERS!$B7:$AK7)-1,"")))}
Press CTRL+SHIFT+ENTER to enter array formulas.

You could then do a pretty simple Pivot Table on the First Month / Revenue. Does something like this suit your needs?

 

[andrews1vt]

Russel,

I really appreciate your help. This does get the job done and I'll just need to get use to how I'm looking at the data!

Thanks a million.