Would like to automate my spreadsheet.

Eddielion

New Member
Joined
Jun 17, 2005
Messages
37
Hello everyone. I'm running Windows 10 and Office 365. I'm looking for a formula or formulas to automate my spreadsheet. Right now this is what I have.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5 " /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5 ;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF ;;"></td><td style="text-align: right;border-left: 1px solid black;background-color: #C0C0C0;;">1</td><td style="text-align: right;background-color: #C0C0C0;;">2</td><td style="text-align: right;background-color: #C0C0C0;;">3</td><td style="text-align: right;background-color: #C0C0C0;;">4</td><td style="text-align: right;background-color: #C0C0C0;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">9/19</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">22</td><td style="text-align: right;;">28</td><td style="text-align: right;;">29</td><td style="text-align: right;border-top: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">My data is in cells A1:F250.</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">9/18</td><td style="text-align: right;;">6</td><td style="text-align: right;;">10</td><td style="text-align: right;;">11</td><td style="text-align: right;;">16</td><td style="text-align: right;;">25</td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Numbers in row 1 goes from; H:BA</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">9/17</td><td style="text-align: right;;">3</td><td style="text-align: right;;">12</td><td style="text-align: right;;">22</td><td style="text-align: right;;">24</td><td style="text-align: right;;">29</td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF " ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF ;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5 ;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">H2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F10,H$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">I2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F10,I$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">J2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F10,J$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">K2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F10,K$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">L2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B2:$F10,L$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">H3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B3:$F11,H$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">I3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B3:$F11,I$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">J3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B3:$F11,J$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">K3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B3:$F11,K$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">L3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B3:$F11,L$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">H4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B4:$F12,H$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">I4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B4:$F12,I$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">J4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B4:$F12,J$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">K4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B4:$F12,K$1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">L4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B4:$F12,L$1</font>)</td></tr></tbody></table></td></tr></table><br />

To change my spreadsheet,goto H1 change how many rows to look at then copy and paste across and also down.
I would like for G1 to be where I add my number of rows to look at. So I put a number there and it changes my sheet automatically.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,692
Office Version
  1. 365
Platform
  1. Windows
How about
=COUNTIF($B2:INDEX($F2:$F250,$G$1),H$1)
 

Eddielion

New Member
Joined
Jun 17, 2005
Messages
37
Am I suppose to enter the formula with CTR, SHIFT, ENTER? because when I enter the formula in H1 and copy it across, it's ok. But when I copy it to H3 down and across it says"The formula in this cell refer to a range that has additional numbers adjacent to it."
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,692
Office Version
  1. 365
Platform
  1. Windows
No need for CSE entry, it's a normal formula.
As for the warning message, you would have got that with your formula as well.
 

Eddielion

New Member
Joined
Jun 17, 2005
Messages
37

ADVERTISEMENT

Okay. Thank you very much Fluff. I really appreciate all the help you've givin me. Thanks again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,692
Office Version
  1. 365
Platform
  1. Windows
Just to check, are you looking to limit the countif to only a few selected rows of data?
For instance only looking at 10 rows out of the 250?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,692
Office Version
  1. 365
Platform
  1. Windows
In that case the formula I provided should be ok.
 

Eddielion

New Member
Joined
Jun 17, 2005
Messages
37
Yes, it's working. Thank you, I appreciate all the help. Thank you again. Eddielion
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,692
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,840
Messages
5,638,656
Members
417,041
Latest member
Molo

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