• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Excel 2007 count items

I have a spreadsheet with two tabs. One is labeled "MAIN" and the other is labeled "Appraisal_Status".

On the MAIN tab in Col A7 I have "Appraisal Assessment and Rating Status" and there are three items that it can be.

In Progress
Not Initiated
COMPLETED
BLANKS

On the Appraisal_Status tab Col F is labeled "Appraisal Assessment and Rating Status".

I need to count In Progress, Not Initiated, Completed & BLANKS and display the count on the Main tab.

Please see the attached.

Thanks.
TEMP-Appraisal-Status.xlsx
0
CMILLER
Asked:
CMILLER
  • 5
  • 4
  • 3
  • +1
3 Solutions
 
Jignesh TharSenior ManagerCommented:
0
 
Naresh PatelTraderCommented:
0
 
NBVCCommented:
You can use a Pivot Table.

Select the column, then Insert Pivot Table.

Click Finish...

Then in the Right Pane, drag the item from top section to Columns section below.  Drag again from top to  Values section below.

Should give counts of each without formulas.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
CMILLERAuthor Commented:
I gave the most points to itjockey because the showed me how to count the blanks.
0
 
NBVCCommented:
Another way to count blanks without having to define range...

=MATCH(REPT("z",255),Appraisal_Status!F:F)-SUM(B8:D8)-1
0
 
Naresh PatelTraderCommented:
Mr. NBVC,

Will you pls explain How your Formula Works i.e. =MATCH(REPT("z",255),Appraisal_Status!F:F)-SUM(B8:D8)-1

Just for knowledge sake, your formula is perfect.
Thanks
0
 
Naresh PatelTraderCommented:
Mr.CMILLER,

Mr.NBVC is formula is more appropriate than me as if you add some in range you have to modify my formula for blank. but if you use "=MATCH(REPT("z",255),Appraisal_Status!F:F)-SUM(B8:D8)-1" then you dont have to modify formula.

mi right Mr.NBVC?

Thanks
0
 
NBVCCommented:
MATCH(REPT("z",255),Appraisal_Status!F:F) finds last row with a text string in it.  It does this be looking for a string that is the "z' character repeated 255 times.  It obviously won't be found, so MATCH()  .. note that it doesn't have the optional Type parameter to find exact match.  Instead if find last match that is smaller than or equal to the search criteria.  All strings are smaller than "z" repeated 255, so it returns position of last string found. (here in row 231).  Then my formula deletes the sum of the other three categories, leaving the true blank count in the range.  The extra -1 at the end is to discount the header.
0
 
Naresh PatelTraderCommented:
Thank You Very Much for Explanation. Really Appreciated.
0
 
Naresh PatelTraderCommented:
Thank you Very much for explanation. really appreciated.
0
 
CMILLERAuthor Commented:
Thanks Guys!

I submitted a request to give NBVC some points.
0
 
NBVCCommented:
It's appreciated, but not necessary.  Just trying to help. :)
0
 
CMILLERAuthor Commented:
Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now