|
Counting rows
How can I count the rows starting with the cell containing "ASSESSMENT
NUMBER" through the next cell containing "PAID STATUS"? Then, how can I assign the resulting count to the rows upon which that count was derived? Here's my spreadsheet: A ASSESSMENT NO 001310350-1 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2010 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.25 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2011 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310366-6 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 70-0016 222.50 .00 70-0060 .00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2010 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.25 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2011 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310380-8 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310380-8 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID |
Counting rows
Hi Gary
'Change Column Ref's to suit =COUNTIF("$A$1:$A$1000", "ASSESSMENT NUMBER") now if you want to count rows based on 2 criteria then =SUMPRODUCT(--($A1:$A1000 = "ASSESSMENT NUMBER"),--($B$1:$B$1000=YourCriteria)) HTH Mick On 1/01/2012 5:17 AM, gary c wrote: How can I count the rows starting with the cell containing "ASSESSMENT NUMBER" through the next cell containing "PAID STATUS"? Then, how can I assign the resulting count to the rows upon which that count was derived? Here's my spreadsheet: A ASSESSMENT NO 001310350-1 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2010 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.25 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2011 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310366-6 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 70-0016 222.50 .00 70-0060 .00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2010 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.25 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2011 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310380-8 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310380-8 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID |
Counting rows
Oops
Correction. =COUNTIF($A$1:$A$1000, "ASSESSMENT NUMBER") On 1/01/2012 11:13 AM, Vacuum Sealed wrote: Hi Gary 'Change Column Ref's to suit =COUNTIF("$A$1:$A$1000", "ASSESSMENT NUMBER") now if you want to count rows based on 2 criteria then =SUMPRODUCT(--($A1:$A1000 = "ASSESSMENT NUMBER"),--($B$1:$B$1000=YourCriteria)) HTH Mick On 1/01/2012 5:17 AM, gary c wrote: How can I count the rows starting with the cell containing "ASSESSMENT NUMBER" through the next cell containing "PAID STATUS"? Then, how can I assign the resulting count to the rows upon which that count was derived? Here's my spreadsheet: A ASSESSMENT NO 001310350-1 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2010 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.25 .00 PAID STATUS UNPAID ASSESSMENT NO 001310350-1 TAX YEAR 2011 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310366-6 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 70-0016 222.50 .00 70-0060 .00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2010 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.25 .00 PAID STATUS UNPAID ASSESSMENT NO 001310379-8 TAX YEAR 2011 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310380-8 TAX YEAR 2008 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 24.50 .00 PAID STATUS UNPAID ASSESSMENT NO 001310380-8 TAX YEAR 2009 TAXABILITY CD 0-00 70-0012 13.00 .00 70-0015 21.00 .00 PAID STATUS UNPAID |
| All times are GMT +1. The time now is 08:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com