Need help in writing a formula with morethan 7 if conditions.

Hi Kishore,

I believe you should create a table on those conditions and then you can use lookup functions (lookup, vlookup etc) to achieve the desired results...
Upload a sample file in case of any issues. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

You sample only has the letters "A" to "G" in column C, what are the real values you have in this column?
What else could there be in this column?

Assuming only single letters or blanks, this array formula might help
In say G2

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter

Here is several solutions, including Marcol.

a) Use filters and you'll get desired number above table (filter Kishore, number 9 or else)
b) Write desired name and letters you want to include

Warning! In a table you have space after eash name

c) count everything (because only A to G are included). Works for XL2003.
d) same as above only XL 2007 version
e) Marcol solution

Have you looked at the workbook that zbor posted in Post#5?

The Cells G2:I2 show you how to handle that situation, put ACDG in H2
If there could be blanks in column C
Change the formula in I2 to ...

Please Login or Register  to view this content.

I asked this in Post #4

You sample only has the letters "A" to "G" in column C, what are the real values you have in this column?
What else could there be in this column?

You would save everybodies' time, including your own, if you were to post a sample of your real workbook.

Okay, have patience, remember time zones, you're some 4-5 hours ahead of me ...

Give this a try ...

With Sheet "Reports"
1/. Make a list of unique Agents
In A2

Please Login or Register  to view this content.

Drag/Fill down until the formula returns blanks, say in this case 75 rows.
If more agents are added to Sheet "Raw Data" Column C they will be detected, this column must be continuous and cannot have blanks.

2/. In B2, this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter
Drag Across to Column H, then down to last used row in Column A

3/. Put the minimum "Satisfaction Level" you require in K2.the
If you leave K2 blank the table will return a count of all the results for each agent
If you put a number in K2 the table will return a count of all the results greater or equal to your expectation for each agent.

Note
I only have access to Excel 2003 at present, someone with a higher version might want to try to improve on the SUMPRODUCT() formula.

[EDIT]
@ kvsrinivasamurthy I think you're a bit behind the curve with this one ...

Your sample book gives a list of models all of which aren't in Sheet "Raw Data",there are also some in that sheet that you haven't listed, is this correct?

1/. I have creaed a list of models that are in Sheet "Raw Data", this is in Column L.

2/. Use this list as the Dynamic Named List "Models"
Refers to:=

Please Login or Register  to view this content.

3/. The Data Validation List in J2 refers to "Models"
Select from this drop-down to get the various counts in the table.

4/. In B2

Please Login or Register  to view this content.

Drag Across and Down.

Is these the results that you are after?

@ kvsrinivasamurthy Have you posted in the correct thread?
Your attachment in post #18 seems unrelated to this one ...