Countif Reference Column Header (Month)

bretgriffin

New Member
Joined
Jul 11, 2007
Messages
6
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).

exceltest.gif


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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jul 11, 2007
Messages
6
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top