Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • 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
Advertise Here
  • 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Go Premium
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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