itsmevic

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

ASKER

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

SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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)<>""))

=COUNTA(High!B:B)-COUNTIF(

=COUNTA(High!B4:B42)-COUNT

=COUNTA(Medium!B:B)-6

=SUMPRODUCT((TRIM(High!B1:

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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