Countif Reference Column Header (Month)

[bretgriffin]

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.

 

[Brian from Maui]

For account 1 try,

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

entered with crtl+shift+Enter

 

[Scott Huish]

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
	A	B	C	D	E	F	G	H	I
1			2007
2	Acct	Start	Jan	Feb	Mar	Apr	May	Jun	Jul
3
4	Account 1	Jan-07	100	200	300	400	500	600	700
5	Account 2	Mar-07			100	200	300	400	500
Sheet1

 

[bretgriffin]

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]

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