Oct 23 2018 03:42 AM
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
Oct 23 2018 07:23 AM
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.
Oct 23 2018 07:32 AM
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.
Oct 23 2018 07:50 AM
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?
Oct 23 2018 08:40 AM
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.
Oct 23 2018 09:09 AM
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.
Oct 23 2018 09:19 AM
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.
Oct 23 2018 09:25 AM
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.
Oct 23 2018 09:50 AM
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.
Oct 23 2018 03:25 PM
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))),"")
Oct 24 2018 02:19 AM
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.