# Excel 2010: Formula Question (Counting Values on Other Worksheets)

Hi Experts!

I'm having a heck of a time with my workbook.  All I need to do is tally up column B's entries on each worksheet (there are 3 of them) and then enter that count on a Summary worksheet.

The worksheet's names are High, Medium and Low.  The problem is, is that my present formula on the "Summary" worksheet i.e. =COUNTA(High!B:B)-8 and =COUNTA(Medium!B:B)-8 and =COUNTA(Low!B:B)-8 doesn't take into account of the multiple headers in column B therefore it's skewing the number count on the Summary worksheet.

I need a formula that will omit the multiple headers in column B and count only cells that have data in them.  Omitting the blank rows?  The example I have attached is the exact format that I'm working with.  In this example you can click on "high, medium or low" and see the formula used on the Summary worksheet.  I've tried using basic if statements but that doesn't seem to help either.

Any help with this is greatly appreciated.
Shanan212

=COUNTA(High!B1:B30)-COUNTIF(High!B1:B30,"="&"Job Title")

Try that for every tab (replacing the tab names)

Make sure your 'job title' dont have extra spaces around it

I thought for sure that formula might work, but for some reason it's still not counting properly.   ) - :
SOLUTION
barry houdini

membership
Create an account to see this answer
Signing up is free. No credit card required.

That didn' t work either Barry.  Not sure what is going on.  Have tried the following formulas with no luck.

=COUNTA(High!B:B)-COUNTIF(High!B:B, "Removable*")-COUNTIF(High!B:B, "User Name*")-COUNTIF(High!B:B, "Uploads*")-COUNTIF(High!B:B, "CD Burning*")-COUNTIF(High!B:B, "Resume*")

=COUNTA(High!B4:B42)-COUNTIF(High!B4:B42,"="&"Job Title")

=COUNTA(Medium!B:B)-6

=SUMPRODUCT((TRIM(High!B1:B30)<>"Job title")*(TRIM(High!B1:B30)<>""))

membership
Create an account to see this answer
Signing up is free. No credit card required.

What's the best way to go through my master template to see if it has leading/trailing spaces?

Column C as you know is the Job Title column.  What I've done is placeds a =GETADSPROP formula so that it reaches out to AD and pulls that person's "Job Title" automatically saving time from having to look it up in ADUC.  Due to the formula there now, if there isn't a username listed it will by default add the following to the cell until it's populated with a username:  i.e. "#VALUE!"

I'm curious if this is what may be throwing the count off due to the fact the formula is looking at the "Job Title" column and therefore sees that #VALUE! listed, it assumes it's the title, and counts it when technically it should not.

I'm not sure really.  The cleaned up spreadsheet you provided Shannan is working beautifully, if I could only get my spreadsheet to work the same!