Major Excel Formula Needed

[Pulse]

I need help with creating a formula that selects a certain value in one column then reads across and then selects only the ones that also fit another criteria.

for instance if i have a column of fruits in stock i want it to select, say, apples. Then i want it to select how many are red from another column, so it fills 2 criteria, apple and red. and then returns the value of how many do apply to both.

is this even possible?

 

[EirePlane]

sounds like you could need nested vlookups...if they are possible

 

[Pulse]

and how would i go about doing that? dont have to make it too detailed.

 

[EirePlane]

tbh im not too sure how you would go about doing it. Just had a look in excel and i dont even think it is possible, sorry.

 

[Pulse]

cheers anyway

 

[suarve]

should be able to that with vb in excel too fairly easily. did a project ages ago at uni doing something similar. i'll have a look tomrrow to see if i saved it.

 

[madmaxx]

You need an array formula - as an example :-

{=SUM(IF(H2:H121="Apple",IF(I2:I121="Red",1,0)))}

This would i think do what you need, if the range of data for the first criteria (i.e. apple) was in H2:H121, and the second criteria (i.e. Red) was in the following column I2:I121. Its an array formula, so you either have to type the 'curly' brackets, or enter it by pressing Shift Alt Enter.

You might need to jiggle the exact syntax a bit, but thats how to do it - def no need to grab any VB for something that simple - unless you really feel the need, of course!
Hope that helps.

 

[Pulse]

{=SUM(IF(H2:H121="Apple",IF(I2:I121="Red",1,0)))}

i can understand this and i would think that it should work but it doesn't,

i'll try and give it in its rightful context.

In column C i have law areas and i want it to select "Contact" then i want it to look and see if a name was supplied on a form (this is in another column (P) with 1 denoting a Yes and a 2 denoting a No) all of this is on a different sheet.

so i would think to use the code
=SUM(IF('Data Entry Sheet'!C:C="Contact",IF('Data Entry Sheet'!P="1",1,0)))

or have i already gone wrong? i dont get what is with the wierd brackets in your example

 

[madmaxx]

The curly brackets are because its an array formula - type the formula, then press shift - alt - enter to enter it - and Excel will add the brackets, and give an answer based on all the values in the ranges specified.
I know the formula works, as i copied it straight out of a workbook I've got here,and its working ok. Have a faf around, and if you still cant make it work, reply, and ill sort it out for ya.