Avatar of itsmevic
itsmevic
Flag for United States of America asked on

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.
Example-Spreadsheet-Worksheet2.xlsm
Microsoft ExcelMicrosoft OfficeSpreadsheets

Avatar of undefined
Last Comment
itsmevic

8/22/2022 - Mon
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
itsmevic

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
itsmevic

ASKER
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)<>""))
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Shanan212

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
itsmevic

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

ASKER
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!
itsmevic

ASKER
I went in and cleaned up the first worksheet "High" of erroneous spaces/data and it appears to be tallying up the numbers correctly.  One thing, I had to remove the =GetAdsProp formula though in order to pull the data correctly.  If there was a way to to keep that formula and just blank out the cell that would be great!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
itsmevic

ASKER
Fantastic feedback from everyone!  Thank you.