Formula Adjustment Needed.

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
I have this formula that another user here created for me, basically it counts cells between 2 reference points, just wondering how i may have it just count the non blank cells.

=IF(Data!AS1="","",MATCH("?*",INDEX(Data!AS:AS,1+MATCH(Data!AT1,Data!AS:AS,0)):Data!AS$1048576,0))
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Not sure how that is counting anything? Match returns the (1st) position of a criteria a range, it does not count.

If you need to count stuff, take a look at using the COUNTIFS() function - it counts all non-blank cells...
=countif(range1,criteria1, range2, criteria2..........................)
 

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
As you can see with columns A:C the formula in column C works fine

=IF(A1="","",IF(A1="end","",MATCH("?*",INDEX(A:A,1+MATCH(B1,A:A,0)):A$1048576,0)))

But what i need is for the same result in column H, i have just given the example in H of what i need using the data in Columns F:G


<b>Excel 2010</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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">test1</td><td style=";">test1</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">test1</td><td style=";">test1</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></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=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">test2</td><td style=";">test2</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">test2</td><td style=";">test2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></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=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">test3</td><td style=";">test3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">test3</td><td style=";">test3</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">data</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">end</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">end</td><td style="text-align: right;;"></td><td style=";"></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 />
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Try this, copied down to the end of the data in column G.

Excel Workbook
FGH
1test1test14
2data
3
4data
5data
6test2test23
7data
8data
9
10test3test33
11data
12data
13end
Count
 

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597

ADVERTISEMENT

Your formula looks to simple, yet it works better than the original, ty
 

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
Oops, sorry i should have said that there is formulas in row G so your COUNTA is also counting them even if they are blank
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Oops, sorry i should have said that there is formulas in row G so your COUNTA is also counting them even if they are blank
In that case try this in H1

=IF(F1="","",COUNTIF(G1:G$13,"?*")-SUM(H2:H$13))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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