Excel 2010 - Unsure How to Proceed

[emily123]

Good Day Everyone!

I'm hoping someone could help me figure out how to accomplish the following in Excel 2010:

- I have a workbook with 16 sheets (one for each year 1997-2012) containing data on individual files. The individual file information is input horizontally (with date in column 'A', name in column 'B' company number in column 'C' and so on...)
- There are a different number of files for each year (1997 has 4, 2010 has 30)
- On sheet 17 I have put the data analysis
- I am looking for a formula that will tell me how many times 'tbd' appears in the worksheet 1997 where the company number in the same row is '1'. Put another way, on sheet 1997 how many 'tbd's (to be determined) are there for company number 1?

I have tried countifs and sumproduct etc. but nothing seems to work correctly. I am hoping there is a formula someone might know of that will search through entire columns as opposed to having to name a specific range (ie search C:C instead of C4:C10).

If anyone is able to provide assistance it would be greatly appreciated!!

Thanks!!!

[RetiredGeek]

Emily,

Welcome to the lounge as a new poster.

Attached is one possible solution. It does however require the use of helper columns to determine the row of the company in the yearly sheets and the years have to be changed if you copy the formulas horizontally but they copy vertically just fine. I'll work on it some more so maybe you can copy w/o any editing. Hope this helps. Also the formulas can probablly be merged to eliminate the the helper cells.

[RetiredGeek]

Emily,

Here's the version w/o helper cells and the formulas are fully copyable.

[emily123]

Sample.xlsx

Thank you very much for your welcome message and assistance with my excel issue!

I attempted using the version without helper columns but couldn't get it to work. I know I'm doing something wrong somewhere along the line...

However, I could get the version with helper columns to work great! However, where there is more than one mention of the same company the formula just accounts for the first instance. Is there a way to amend the formula to account for multiple mentions of the same company? I have attached a sample workbook as an example.

Thanks again for all your help!!

[RetiredGeek]

Emily,

I got the no helper cell formula to work on your data:
=COUNTIF(INDIRECT(LEFT(B$3,4) & "!G" & MATCH($A4,INDIRECT(LEFT(B$3,4) & "!$D:$D"),0) & ":N" & MATCH($A4,INDIRECT(LEFT(B$3,4) & "!$D:$D"),0)),"TBD")

However, I can't seem to figure our how to make it see more than one row in a sheet. I toyed around with SumProduct which seems to be the answer but I haven't used it enough to know why my attempts cause errors. Hopefully someone else will jump in who has more experience with the versitle SumProduct function. If not I'll tackle it via VBA when I have a chance.

[rory]

If you convert the tables on the sheets to actual Tables, it's pretty straightforward. See attached.

[renigar]

I couldn't figure out how to not have a set column range, but did come up with a formula that I believe gives you the correct answers your looking for. I'm sure there is a better way to do it, but I don't have enough knowledge yet. You can copy & paste the formula into your other year columns and use find and replace on the selected year column to update the year sheet references in the formulas.