Count If with formula or vba?

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,971
Office Version
  1. 2019
Platform
  1. Windows
peace.,

in range A10:A20.

If empty cell present then take the last row number in range which is A20 and subtract with the row number where the empty cell is present. Output the resulting value in cell E1.

Note: If more than 1 empty cell in range A10:A20 then apply the formula to the first empty cell from the top.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
Maybe

=$A$20-IF(COUNTIF($A$10:$A$20,"="),INDEX(ROW($A$10:$A$20),MATCH(TRUE,INDEX($A$10:$A$20="",0),0)),0)

M.
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,971
Office Version
  1. 2019
Platform
  1. Windows
Maybe

=$A$20-IF(COUNTIF($A$10:$A$20,"="),INDEX(ROW($A$10:$A$20),MATCH(TRUE,INDEX($A$10:$A$20="",0),0)),0)

M.


Not exactly what I was expecting but this will work for me if column A is auto numbered from row 10 till last data..

Thxxx :)
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,971
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

The column A has alphabet data and numeric data was to be collected from row number if there is a formula for such.. =row maybe? . so I changed the destination formula so that column A carry the same numbering like the row has on A10 to A20.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
The column A has alphabet data and numeric data was to be collected from row number if there is a formula for such.. =row maybe? . so I changed the destination formula so that column A carry the same numbering like the row has on A10 to A20.

This is ok?

=ROW($A$20)-IF(COUNTIF($A$10:$A$20,"="),INDEX(ROW($A$10:$A$20),MATCH(TRUE,INDEX($A$10:$A$20="",0),0)),0)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,715
Members
414,401
Latest member
grenona2020

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