Trying To Count A Specific # of Columns

[msj2487]

Hi everyone, I'm new to this site, so I apologize if this has been solved in the past, and I also apologize as I am an amateur with Excel, but I am trying to count a specific # of columns in a spreadsheet.

The concept of the spreadsheet is to display the number of occurrences of a specific criteria based on the value of another cell.

Essentially, I have a list of years going from AI to DL (82 columns of years) and a list of teams from rows 5 through 134 (130 rows of teams) and the values of the cells between these ranges are where the teams finished the season as a ranked team.

What I want my spreadsheet to display is the ability to show a specific criteria, for instance the # of times a team finished ranked #1 , but I want the return value to be determined based off of a searchable cell (for arguments sake, I currently have it set as D2) to where I can input 5 in cell D2 and Excel will count the # of times (countif formula) that the team in Row 12 (just used as an example row) finished ranked #1 during the past 5 years (because D2 is telling it to search 5 columns), and therefore it would only count 5 of the 82 columns for row 12 and also only display the number of times that team finished #1 inside of those 5 columns.

Moreover, if I then changed the searchable cell (D2) to 10, I could see how many times the team finished ranked #1 in the past 10 years, ie. it would 10 of the 82 columns, etc.

So far I've been using the COUNTIF formula, but I can't figure out how to only count a specific # of columns from the table based on the value from the searchable cell (D2).

A work around I have come up with (since again I'm very basic with Excel) is to pair it with an IF formula and sequentially list all possible outcomes someone might search in D2, but that is obviously exhausting and I would assume extremely unnecessary. So the formula that works right now would be:

=IF($D$2=1,(COUNTIF(AI5:AI5,"=1")),IF($D$2=2,(COUNTIF(AI5:AJ5,"=1")),IF($D$2=3,COUNTIF(AI5:AK5,"=1")), and so on until DL5.

I imagine there is an extremely, more condensed formula that could work here and any help would be greatly appreciated!

Thanks so much!

 

[mrshl9898]

Try this:

=COUNTIF(INDIRECT(ADDRESS(5,35)&":"&ADDRESS(5,34+D2)),1)

ADDRESS(rownum,colnum) will work out the range you need.

 

[msj2487]

mrshl9898, I apologize for not replying sooner, got caught up with the weekend. However, unfortunately, I could not get that formula to work for me.

Essentially, I have the following spreadsheet:

View Last:	83	Years																												Not Ranked
										0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21
Total Games Played	Wins	Losses	Ties	FCS Opponents	Win %	Rank	Total	% of Top 25 Finishes	Average Top 25 Ranking		2018	2017	2016	2015	2014	2013	2012	2011	2010	2009	2008	2007	2006	2005	2004	2003	2002	2001	2000	1999	1998
103	57	46	0	9	55%	69	6	7%	14			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	13

<colgroup><col width="84" style="width: 63pt;"><col width="44" style="width: 33pt;"><col width="52" style="width: 39pt;"><col width="36" style="width: 27pt;"><col width="83" style="width: 62pt;"><col width="41" style="width: 31pt;"><col width="40" style="width: 30pt;"><col width="85" style="width: 64pt;"><col width="80" style="width: 60pt;"><col width="69" style="width: 52pt;"><col width="15" style="width: 11pt;"><col width="41" style="width: 31pt;"><col width="41" span="18" style="width: 31pt;"><col width="60" style="width: 45pt;"><col width="41" style="width: 31pt;"></colgroup><tbody>
</tbody>

Cell D2 is the square with the "83" in it and below are cells C4 - BC4 for the titles and C5 - BC5 for the values.

The goal for the spreadsheet is to put a formula into J5 that will only count the total number of columns from AI5 - DM5 based on the value in D2.

So if D2 is "5" then J5 will count the number of occurrences where this team finished in the Top 25 of the polls based on the values from AI5 - AM5; which in the above example would be 0.

But if D2 was "21" then J5 would be "1" because they finished #13 in 1998; 21 years ago.

Thanks again for any assistance!