Counting Blank Cells with specific criteria

E

error_free

Board Regular
Joined
Aug 16, 2009
Messages
69
Hi All,

I am new to this forum, just joined today.

I am having trouble with counting blank cells with specific criteria.

I have a worksheet that changes data every week. Same number of columns but the actual data rows will be different. eg. one week i will have 1000 rows but the next week I might have 900 rows or 1500 rows.

<table style="border-collapse: collapse; width: 1002pt;" border="0" cellpadding="0" cellspacing="0" width="1333"><col style="width: 56pt;" width="74"><col style="width: 25pt;" width="33"><col style="width: 53pt;" width="70"><col style="width: 161pt;" width="215"><col style="width: 53pt;" width="70"><col style="width: 48pt;" span="2" width="64"><col style="width: 56pt;" width="75"><col style="width: 128pt;" width="170"><col style="width: 155pt;" width="206"><col style="width: 56pt;" width="75"><col style="width: 45pt;" width="60"><col style="width: 68pt;" width="90"><col style="width: 50pt;" width="67"><tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt; width: 56pt;" width="74" height="21">
</td><td class="xl67" style="border-left: medium none; width: 25pt;" width="33">
</td><td class="xl67" style="border-left: medium none; width: 53pt;" width="70">
</td><td class="xl68" style="border-left: medium none; width: 161pt;" width="215">
</td><td class="xl67" style="border-left: medium none; width: 53pt;" width="70">
</td><td class="xl69" style="border-left: medium none; width: 48pt;" width="64">
</td><td class="xl70" style="border-left: medium none; width: 48pt;" width="64">
</td><td class="xl68" style="border-left: medium none; width: 56pt;" width="75">
</td><td class="xl68" style="border-left: medium none; width: 128pt;" width="170">
</td><td class="xl71" style="border-left: medium none; width: 155pt;" width="206">
</td><td class="xl68" style="border-left: medium none; width: 56pt;" width="75">
</td><td class="xl72" style="border-left: medium none; width: 45pt;" width="60">
</td><td class="xl73" style="width: 68pt;" width="90">
</td><td class="xl73" style="width: 50pt;" width="67">
</td></tr></table>Column A has codes such as CVVLUWW as well as blank cells
Column G has number of days 0 to anything
What I want to achieve is count number of blank cells in Column A which falls in to ranges of <45 days, 45-90 days, 90-120 days, 120-150days, 150-180 days and 180< days.

The spreadsheet has data from Column "A" to Column "AC", the number of rows will change every week.

If anyone can give me a hand with this, it would be great as I have spent 3 weeks trying to figure out how to do this.
<table style="border-collapse: collapse; width: 1336px; height: 157px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 56pt;" width="74"> <col style="width: 25pt;" width="33"> <col style="width: 53pt;" width="70"> <col style="width: 161pt;" width="215"> <col style="width: 53pt;" width="70"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 56pt;" width="75"> <col style="width: 128pt;" width="170"> <col style="width: 155pt;" width="206"> <col style="width: 56pt;" width="75"> <col style="width: 45pt;" width="60"> <col style="width: 68pt;" width="90"> <col style="width: 50pt;" width="67"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl64" style="height: 15.75pt; width: 56pt;" width="74" height="21">
</td><td class="xl65" style="border-left: medium none; width: 25pt;" width="33">
</td><td style="vertical-align: top;">
</td><td class="xl66" style="border-left: medium none; width: 161pt;" width="215">
</td><td class="xl65" style="border-left: medium none; width: 53pt;" width="70">
</td><td class="xl67" style="border-left: medium none; width: 48pt;" width="64">
</td><td style="vertical-align: top;">
</td><td class="xl68" style="border-left: medium none; width: 48pt;" width="64">
</td><td class="xl66" style="border-left: medium none; width: 56pt;" width="75">
</td><td class="xl66" style="border-left: medium none; width: 128pt;" width="170">
</td><td class="xl69" style="border-left: medium none; width: 155pt;" width="206">
</td><td class="xl66" style="border-left: medium none; width: 56pt;" width="75">
</td><td class="xl70" style="border-left: medium none; width: 45pt;" width="60">
</td><td class="xl71" style="width: 68pt;" width="90">
</td><td class="xl71" style="width: 50pt;" width="67">
</td></tr><tr><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td style="vertical-align: top;">
</td><td>
</td><td align="right">
</td><td>
</td><td style="vertical-align: top;">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td>
</td><td class="xl72" align="right">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td style="vertical-align: top;">
</td><td>
</td><td align="right">
</td><td>
</td><td style="vertical-align: top;">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td>
</td><td class="xl72" align="right">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td style="vertical-align: top;">
</td><td>
</td><td align="right">
</td><td>
</td><td style="vertical-align: top;">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td>
</td><td class="xl72" align="right">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td style="vertical-align: top;">
</td><td>
</td><td align="right">
</td><td>
</td><td style="vertical-align: top;">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td>
</td><td class="xl72" align="right">
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td align="right">
</td><td style="vertical-align: top;">
</td><td>
</td><td align="right">
</td><td>
</td><td style="vertical-align: top;">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td>
</td><td class="xl72" align="right">
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
 
Sort by date Sort by votes
it works!!!! thanks a lot!

Another thing I wanted to ask was, as the number of rows change on a weekly basis, how do I amend the formula so that it will pick up the change in rows automatically without having to be changed manually.
 
Upvote 0
error_free said:
it works!!!! thanks a lot!

Another thing I wanted to ask was, as the number of rows change on a weekly basis, how do I amend the formula so that it will pick up the change in rows automatically without having to be changed manually.
Click to expand...

If you are on Excel 2003, you can convert the data area into a list (Data|List|Create List) or if Excel 2007 into a table. These allow the formulas to automatcally update their references.

Prior to 2003, you can make use of the so-called dynamic named ranges.

Assuming that the relevant data is on Sheet1 starts at Row 2...

Define Size as referring to:

=MATCH(REPT("z",255),Sheet1!$A:$A)-ROW(Sheet1!$A$2)+1

Define Codes as referring to:

=OFFSET(Sheet1!$A$2,0,0,Size)

Define Days as referring to:

=OFFSET(Sheet1!$G$2,0,0,Size)

Now you can invoke...

=SUMPRODUCT(--(Codes=""),--(Days >=0),--(Days < 45))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
1K
smozgur
smozgur
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
422
dilshod_k
D
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
1K
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Share this page

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
Back
Top