IF Formula trouble

Copper Contributor

Hi All

I have a formula which lists names looking at a criteria in a cell.

 

=IFERROR(INDEX('Year 5'!$A$3:$A$62,SMALL(IF('Year 5'!$C$3:$C$62="1",ROW('Year 5'!$A$3:$A$62)-ROW('Year 5'!$A$3)+1),ROWS(C$4:C4))),"")

 

I now want to be able to add an and in.  I want the formula to list names that have a 1 in cells c3:c62 and have a 1 in cells I3:I62

 

Im sure this is simple for someone but I cant get it to work.

 

Thank you

Ian

10 Replies

Hello,

 

have you tried using Countif formula? 

 

=countif('Year 5'!$A$3:$A$62, $A1)

 

the second range should be the staring cell address of your second list. If the formula returns 1 or more then this name is already exists in your main table.

Hi

I am not wanting to count the names.  The formula at the moment looks at the list of names in a3:a62 and makes a new list of any names that have a one in their corresponding cell in column c.  I still want to return the names but the ones that have a 1 in column c and column I.

Hello,

 

can you add your excel file? and describe what you want to achive? As I have understood you have C and I colomns with some values. and you want to look for names where there is 1 on the correspondig cells in C and I column? Is it correct?

Yes, but when those values are found it then lists the names from column a in the cell and the cells below it.  Ill try and make a simplified version as there is a lot of data in the sheet I am using and post it.

do you have to do this with formula? the easiest way is to use a pivot table. filter the data and get required data with getpivotdata function. or you can use Pivot Table as it is.

I have never used a pivot table but if it can get me what I want that is fine.  I have made a very simple version of sheet.  Column a is name, b is info a and c is info b.  In column F, I would like a list of names that have 1 for both info a and info b.  My original formula listed the names but with only one condition, info a.

Thank you.

Here is the pivot table version. You can create a table for your main table if you wish. Which will allow a dynamic range which means the range will expand whenever you add some data. and then refresh pivot table to reflect the changes. hope this helps.

I also add another way to do this with formula. After writing the formula you have to press SHIFT+CTRL+ENTER. The formula is a bit complex. it is an array formula and can find what you need with multiple criterias.

Just for the collection, non-array formulas could be

=IFNA(INDEX($B$4:$B$11,MATCH(1,INDEX(NOT(COUNTIF($G$3:$G3,$B$4:$B$11))*($C$4:$C$11=$G$2)*($D$4:$D$11=$H$2),0,),0),),"")

or

=IFNA(LOOKUP(2,1/NOT(COUNTIF($H$3:$H3,$B$4:$B$11))/($C$4:$C$11=$G$2)/($D$4:$D$11=$H$2),$B$4:$B$11),"")

or

=IFERROR(INDEX(B:B,AGGREGATE(15,6,1/($C$4:$C$11=$G$2)/($D$4:$D$11=$H$2)*ROW($B$4:$B$11),ROW()-ROW($I$3))),"")

Thank you to you both for all your help.  I didn't really understand how to get the pivot table into my sheet so I have tried the third formula from Sergi and it has worked brilliantly thank you.

 

Thank you to you both for all your help.