# Countif Reference Column Header (Month)

#### bretgriffin

##### New Member
Hi guys,

I have a spreadsheet that I track revenue in by month. I'm trying to put together a formula that will return the first month revenue is received on a new account. The months are laid out across the columns, with the year in a merged cell above the months (I have multiple years next to each other in the same spreadsheet). Here's an example (I've manually entered those dates to show the goal).

I think it starts with countif, but I"m not sure how to make it return the headers.

Thanks for the help.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Brian from Maui

##### MrExcel MVP
For account 1 try,

=INDEX(C4:N4,MATCH(TRUE,C4:N4<>0,0))

entered with crtl+shift+Enter

#### Scott Huish

##### MrExcel MVP
Brian from Maui:
I think the op is trying to return a Month/Year combination not the amount.

bretgriffin:
This assumes the year in C1 and that the months are text and always go from January to December.

In B4:
=DATE(\$C\$1,MATCH(TRUE,C4:N4>0,0),1)
Confirm with CTRL-SHIFT-ENTER rather than just Enter.
Copy down.

Format these cells as mmm-yy
Book3
ABCDEFGHI
12007
2AcctStartJanFebMarAprMayJunJul
3
4Account 1Jan-07100200300400500600700
5Account 2Mar-07100200300400500
Sheet1

#### bretgriffin

##### New Member
This works great for 2007. What would I do when I put 2008 next to 2007? Would I have to change the reference for new accounts to point to 2008's headers or would I be able to extend the index over?

#### Scott Huish

##### MrExcel MVP
With a small change to the formula:
=DATE(C\$1,MATCH(TRUE,C4:N4>0,0),1)

as long as everything is in the same relative position, it should be fine.

For example, year in first set was in C1, in second set should be in Q1

Replies
2
Views
432
Replies
3
Views
420
Replies
5
Views
578
Replies
4
Views
761
Replies
7
Views
330

1,181,373
Messages
5,929,571
Members
436,679
Latest member
helly123

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