Link to home
Create AccountLog in
Avatar of itsmevic
itsmevicFlag 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
Avatar of Shanan212
Shanan212
Flag of Canada image

=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
Avatar of itsmevic

ASKER

I thought for sure that formula might work, but for some reason it's still not counting properly.   ) - :
SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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)<>""))
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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!
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!
Fantastic feedback from everyone!  Thank you.