x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 232

# 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.

Thanks.
TEMP-Appraisal-Status.xlsx
0
CMILLER
• 5
• 4
• 3
• +1
3 Solutions

Senior ManagerCommented:
0

0

Commented:
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

Author Commented:
I gave the most points to itjockey because the showed me how to count the blanks.
0

Commented:
Another way to count blanks without having to define range...

=MATCH(REPT("z",255),Appraisal_Status!F:F)-SUM(B8:D8)-1
0

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

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

Commented:
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

Thank You Very Much for Explanation. Really Appreciated.
0

Thank you Very much for explanation. really appreciated.
0

Author Commented:
Thanks Guys!

I submitted a request to give NBVC some points.
0

Commented:
It's appreciated, but not necessary.  Just trying to help. :)
0

Author 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.