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
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
Last Comment
itsmevic
ASKER
I thought for sure that formula might work, but for some reason it's still not counting properly. ) - :
SOLUTION
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.
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(
=COUNTA(High!B4:B42)-COUNT
=COUNTA(Medium!B:B)-6
=SUMPRODUCT((TRIM(High!B1:
=COUNTA(High!B:B)-COUNTIF(
=COUNTA(High!B4:B42)-COUNT
=COUNTA(Medium!B:B)-6
=SUMPRODUCT((TRIM(High!B1:
ASKER CERTIFIED SOLUTION
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.
ASKER
What's the best way to go through my master template to see if it has leading/trailing spaces?
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!
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!
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!
ASKER
Fantastic feedback from everyone! Thank you.
Try that for every tab (replacing the tab names)
Make sure your 'job title' dont have extra spaces around it