Home

IF Formula trouble

%3CLINGO-SUB%20id%3D%22lingo-sub-276800%22%20slang%3D%22en-US%22%3EIF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276800%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20which%20lists%20names%20looking%20at%20a%20criteria%20in%20a%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX('Year%205'!%24A%243%3A%24A%2462%2CSMALL(IF('Year%205'!%24C%243%3A%24C%2462%3D%221%22%2CROW('Year%205'!%24A%243%3A%24A%2462)-ROW('Year%205'!%24A%243)%2B1)%2CROWS(C%244%3AC4)))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20now%20want%20to%20be%20able%20to%20add%20an%20and%20in.%26nbsp%3B%20I%20want%20the%20formula%20to%20list%20names%20that%20have%20a%201%20in%20cells%20c3%3Ac62%20and%20have%20a%201%20in%20cells%20I3%3AI62%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIm%20sure%20this%20is%20simple%20for%20someone%20but%20I%20cant%20get%20it%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-276800%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277311%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277311%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20to%20you%20both%20for%20all%20your%20help.%26nbsp%3B%20I%20didn't%20really%20understand%20how%20to%20get%20the%20pivot%20table%20into%20my%20sheet%20so%20I%20have%20tried%20the%20third%20formula%20from%20Sergi%20and%20it%20has%20worked%20brilliantly%20thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20to%20you%20both%20for%20all%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277178%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277178%22%20slang%3D%22en-US%22%3E%3CP%3EJust%20for%20the%20collection%2C%20non-array%20formulas%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(INDEX(%24B%244%3A%24B%2411%2CMATCH(1%2CINDEX(NOT(COUNTIF(%24G%243%3A%24G3%2C%24B%244%3A%24B%2411))*(%24C%244%3A%24C%2411%3D%24G%242)*(%24D%244%3A%24D%2411%3D%24H%242)%2C0%2C)%2C0)%2C)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(LOOKUP(2%2C1%2FNOT(COUNTIF(%24H%243%3A%24H3%2C%24B%244%3A%24B%2411))%2F(%24C%244%3A%24C%2411%3D%24G%242)%2F(%24D%244%3A%24D%2411%3D%24H%242)%2C%24B%244%3A%24B%2411)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(B%3AB%2CAGGREGATE(15%2C6%2C1%2F(%24C%244%3A%24C%2411%3D%24G%242)%2F(%24D%244%3A%24D%2411%3D%24H%242)*ROW(%24B%244%3A%24B%2411)%2CROW()-ROW(%24I%243)))%2C%22%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276982%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276982%22%20slang%3D%22en-US%22%3E%3CP%3EI%20also%20add%20another%20way%20to%20do%20this%20with%20formula.%20After%20writing%20the%20formula%20you%20have%20to%20press%20SHIFT%2BCTRL%2BENTER.%20The%20formula%20is%20a%20bit%20complex.%20it%20is%20an%20array%20formula%20and%20can%20find%20what%20you%20need%20with%20multiple%20criterias.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276974%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276974%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20the%20pivot%20table%20version.%20You%20can%20create%20a%20table%20for%20your%20main%20table%20if%20you%20wish.%20Which%20will%20allow%20a%20dynamic%20range%20which%20means%20the%20range%20will%20expand%20whenever%20you%20add%20some%20data.%20and%20then%20refresh%20pivot%20table%20to%20reflect%20the%20changes.%20hope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276970%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276970%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20never%20used%20a%20pivot%20table%20but%20if%20it%20can%20get%20me%20what%20I%20want%20that%20is%20fine.%26nbsp%3B%20I%20have%20made%20a%20very%20simple%20version%20of%20sheet.%26nbsp%3B%20Column%20a%20is%20name%2C%20b%20is%20info%20a%20and%20c%20is%20info%20b.%26nbsp%3B%20In%20column%20F%2C%20I%20would%20like%20a%20list%20of%20names%20that%20have%201%20for%20both%20info%20a%20and%20info%20b.%26nbsp%3B%20My%20original%20formula%20listed%20the%20names%20but%20with%20only%20one%20condition%2C%20info%20a.%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276964%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276964%22%20slang%3D%22en-US%22%3E%3CP%3Edo%20you%20have%20to%20do%20this%20with%20formula%3F%20the%20easiest%20way%20is%20to%20use%20a%20pivot%20table.%20filter%20the%20data%20and%20get%20required%20data%20with%20getpivotdata%20function.%20or%20you%20can%20use%20Pivot%20Table%20as%20it%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276942%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276942%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20but%20when%20those%20values%20are%20found%20it%20then%20lists%20the%20names%20from%20column%20a%20in%20the%20cell%20and%20the%20cells%20below%20it.%26nbsp%3B%20Ill%20try%20and%20make%20a%20simplified%20version%20as%20there%20is%20a%20lot%20of%20data%20in%20the%20sheet%20I%20am%20using%20and%20post%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276914%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276914%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20add%20your%20excel%20file%3F%20and%20describe%20what%20you%20want%20to%20achive%3F%20As%20I%20have%20understood%20you%20have%20C%20and%20I%20colomns%20with%20some%20values.%20and%20you%20want%20to%20look%20for%20names%20where%20there%20is%201%20on%20the%20correspondig%20cells%20in%20C%20and%20I%20column%3F%20Is%20it%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276905%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276905%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20am%20not%20wanting%20to%20count%20the%20names.%26nbsp%3B%20The%20formula%20at%20the%20moment%20looks%20at%20the%20list%20of%20names%20in%20a3%3Aa62%20and%20makes%20a%20new%20list%20of%20any%20names%20that%20have%20a%20one%20in%20their%20corresponding%20cell%20in%20column%20c.%26nbsp%3B%20I%20still%20want%20to%20return%20the%20names%20but%20the%20ones%20that%20have%20a%201%20in%20column%20c%20and%20column%20I.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276901%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20trouble%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276901%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehave%20you%20tried%20using%20Countif%20formula%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dcountif(%3CSPAN%3E'Year%205'!%24A%243%3A%24A%2462%2C%20%24A1)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ethe%20second%20range%20should%20be%20the%20staring%20cell%20address%20of%20your%20second%20list.%20If%20the%20formula%20returns%201%20or%20more%20then%20this%20name%20is%20already%20exists%20in%20your%20main%20table.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ian Tobin
Occasional 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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies