Multiple conditions formula

[Dohko]

Hi,

I need to know if it's possible to do the following in a one formula from the following data.

studyID Review IDno Color
A Normal 1233 Blue
R Special 1233 Blue
A Normal 1234 Red
B Normal 2222 Red
R Special 2222 Red
B Normal 2228 Yellow
C Normal 3859 Yellow
R Special 3859 Black
C Normal 3477 Black
D Normal 4447 Green
D Normal 4448 Green
R Special 4448 Green

I need to return a number depending on which color is selected.
Let's say Blue=1, Red=2, Yellow=3,Black=4, Green=5
But it is conditioned by the following.
So for example I need to count the entries in which Idno=Idno where review=Special and
study=a
So if 1233=1233 and study = A it should return a 3.
I need to reference a Whole column not just a defined set.

So basically I need to check if ID's are the same, then specify if its special or normal and the color.

Thanks for any insights.

[ron2k_1]

It's definitely possible using index/match or SUMPRODUCT. Please look into SUMPRODUCT, there are tons of material regarding this formula on this forum and on the net. Or attach a spreadsheet with some sample data and specify clearly what results you want and where and what format you want them.

[DonkeyOte]

Could you explain how you arrive at 3 for your example given there are no records that meet your criteria.

Please also outline:

a) which version of XL you are using

b) whether more than 1 "colour" may exist for any given combination

[DonkeyOte]

Per your other thread regards cross posting

http://www.mrexcel.com/forum/showthread.php?t=501999

Those who repeatedly cross post without providing links invariably have their posting rights suspended - and in general those persons will be largely ignored by those who frequent the on line communities most.

[Dohko]

Isnt this a different forum?

[DonkeyOte]

Isnt this a different forum?

Yes - that is rather the point.

If you spent 30 minutes of your lunch break helping someone resolve a problem only to discover later that someone else unknown to you at the time had fixed the same problem 5 minutes after you had started - wouldn't you be a little displeased at having had your time wasted ?

Please think about your actions - do not abuse the good nature of those willing to assist you (for free).

[Dohko]

I'm using Excel 2007.

And my mistake
If 1233=1233 then It should Print the result in two columns
If 1233=1233 and Review =Normal then Print the color in this case Blue = 1
If 1233=1233 and Review =Special then Print the color in this case Blue = 1

Special Normal
1 1

[Dohko]

Here's a copy of the spreadsheet with the desired results.

[Dohko]

Sorry, I just did that because I wasn't getting any answers in the other forum.
Hopefully with the spreadsheet I just posted it's clearer what need.

[DonkeyOte]

You seem to have "not had answers" a lot ... I hope your attachment helps you get a prompt resolution also.

[ron2k_1]

You're lucky you only got a slap on the hand the first time you did it. I got two infractions the first time I pulled that up!

I still don't understand what you mean by "Print the color in this case..." Print what? The whole table or just that row?

And I still don't get what determines your colour. And your objective overall...

Please clarify as much as possible. Don't assume we understand, spend as much time as needed to let us understand what your objective is. The more the merrier....

[Dohko]

So basically given the table or spreadsheet posted.
Basically I need a summary table.
Normal Special

I need to enter number depending on the color. So for example this are the codes.
Blue=1, Red=2, Yellow=3,Black=4, Green=5

So IF ID's in column C match, then I need to check which color it is an depending on which color then display the appropiate number.
So like I mentioned when 1233=1233 match, I need to display in the Normal and Special Columns a 1, because 1 = Blue. In some cases One will be for example Blue and the other red. So This would be a 1 and 2.

Perhaps this can't be done with a formula, and I need to use VBA but I appreciate any help.

Thanks

[royUK]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

Dohko: read the rules & follow them. The next time you are reported for x-posting without a link I will ban you