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

#### andrews1vt

##### New Member
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.

-Frustrated Controller.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Russell Hauf

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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
1CUSTOMER201401201402201403201404201405201406201407201408201409201410201411201412201501201502201503201504201505201506201507201508201509201510201511201512201601201602201603201604201605201606201607201608201609201610201611201612
210000001066849404050304050505040500401030301005050205010020
3204971031800583000102050503050504004030405010400504004040
43000000000000000200020205050102003010501004050050040
540072853558933040301040400105020040201050000403040201030
65083945672144302050104050505050020010501030503030002000
760697972531083502010300101050405020501010400505002040405030
CUSTOMERS

Then on another sheet, I have the calculations:

Excel 2012
ABC
1CUSTOMERFIRST MONTHFIRST MONTH REVENUE
2120140710
322014024
4320150420
542014037
652014028
762014026
CALCS
Cell Formulas
RangeFormula
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

##### New Member
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.

Replies
5
Views
484
Replies
3
Views
329
Replies
1
Views
537
Replies
1
Views
1K
Replies
1
Views
300

1,191,686
Messages
5,988,004
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back