[jweb_dev]

I am trying to automate a Summary Report by creating a formula that Counts the number of Items across (3) categories.

The complication I cannot get past is that one of the three categories has a Non-Standard range of values. I created a lookup table to try to make the formula easier - still cannot figure out a Formula to work(may require a function?).

I am trying to aviod a macro that physically seperates the source data into (4) seperate spreadsheets, which then can then have hard-coded formulas based on the four spreadsheets for each Company (CoA, CoB, both CoA & CoB, Neither CoA or CoB).

I wish Excel 2003 supported CountIFS(), but even with Excel 2007, I still cannot figure out how to include the complex Range. Ultimately I want a solution that is compatible with Excel 2003.

Source Data: Sheet 1

Lookup Table(for criteria with Non-Standard range of values): Sheet 2

Summary Report: Sheet 3

[Zack Barresse]

Hello there, and welcome to the board!

Am I right in assuming these are three separate files you're talking about here? Or are they three separate sheets in the same workbook? If separate files, why not combine the data into one book?

Also, if you're looking for a summary report, have you looked into Pivot Tables? Are you able to post a sample file? the data you posted doesn't tell us much. We cannot determine what data is in what cell(s)/sheet(s)/book(s).

[jweb_dev]

Thanks for the quick response! I am a new member to the forum and am looking forward to both receiving and giving answers to questions.

I have attached the xls file to make it easier to work on.

Pivot table is not going to work unless I summarize the data into a new list where I could normalize the data in the column with the Non-standard range of values.... trying to eliminate duplicating the data.

Also, the source data will be dynamic - updated regularly. So I wanted the Summary Rpt to be automated(values refreshed automatically for Mgt personell).

[Zack Barresse]

Originally Posted by jweb_dev

... unless I summarize the data into a new list where I could normalize the data in the column with the Non-standard range of values.... trying to eliminate duplicating the data.

Yes!! This is what you want. If you can work this way, you'll be much better off.

Now, with this file, you're looking for formulas in the yellow colored cells? The High, Medium and Low, returning values for each company/dept?

[jweb_dev]

Yes - I am looking for formulas in the Yellow Cells to Count the number of occurances. I have had no luck using Excel 2003(See the attached xls file). To further cause issue... there seems to be corruption in the file(or an Excel Bug) because none of the formulas in Cells above Row 17 will work if they are pasted in Rows 17 or below....the exact same formulas will not work!

FYI - The formulas I do have in Rows 2-16 do not work (they are not accurate, and are amazingly producing inconsistent results! The results should be the exact same numbers unless the formula is somehow not valid).

The closest I have come to getting the formulas to work is using Excel 2007 with the CountIFS(). But No one else at work uses Excel 2007, so that is no good.

[Zack Barresse]

You can do this easily in 2003, but the problem you'll run into is how you have your data structured. Take this data...

Originally Posted by A3:D3

Item 1 CoA Dept A high

Originally Posted by A5:D5

Item 3 CoA,CoB Dept B medium

Originally Posted by A8:D8

Item 6 CoA,CoB Dept A medium

Originally Posted by A12:D12

Item 10 CoA,CoX Dept B high

Originally Posted by A15:D15

Item 13 CoA,CoZ Dept C medium

These are all fields for CoA that matched. See, according to your key, you have double matching values for CoA/DeptA for both High and Medium? That's what your key states and that's how it pulls the data out when matching against your key. I've put it in a workbook with your data, you should be able to see what I'm talking about.

[jweb_dev]

So avoid multiple IFs by creating a single compound Key by combining all the Lookup columns into a new column - Brilliant.

Thanks for the quick response and providing a solution that does exactly what I needed!

[Zack Barresse]

Yup. The key is the unique lookup column you can create. It is kind of a hybrid between your data table and your 'key' table on the next sheet, but it does effectively give you more options for your data set.

The other thing to think about is how you have your data setup in the third sheet. If you didn't have spaces between your data you could effectively just copy down your formula(s) to all the relevant cells. That would negate any need to input the formula(s) multiple times. But I thin you see the jist of it.