SOLVED
Home

Help to make an array formula which select only information fitting certain criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-756253%22%20slang%3D%22en-US%22%3EHelp%20to%20make%20an%20array%20formula%20which%20select%20only%20information%20fitting%20certain%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756253%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20large%20number%20of%20products%20and%20criteria.%20I%20wish%20to%20show%20the%20term%20%22yes%22%20for%20restriciiton%20if%20my%20result%20breaches%20one%20of%20my%20tolerance%20criteria.%20Due%20to%20the%20large%20number%20of%20criteria%20and%20products%2C%20i%20believe%20i%20need%20an%20index%20match%20array%20formula%20or%20similar.%20I%20have%20attached%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20kindly%20for%20your%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-756253%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756357%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20make%20an%20array%20formula%20which%20select%20only%20information%20fitting%20certain%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756357%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20inserted%20a%20row%20at%20row%2025%3C%2FP%3E%3CP%3EI%20then%20copied%20your%20rates%20from%20D9%3AD14%20and%20Transposed%20them%20to%20D26%3AJ26%3C%2FP%3E%3CP%3E(You%20are%20missing%20YEN%20from%20your%20list%20so%20I%20inserted%20that%20at%20row%2012)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20in%20I5%20then%20becomes%3C%2FP%3E%3CP%3E%3DIF(SUMPRODUCT(--(D30%26gt%3B%24D%2426)%2B(E30%26gt%3B%24E%2426)%2B(F30%26gt%3B%24F%2426)%2B(G30%26gt%3B%24G%2426)%2B(H30%26gt%3B%24H%2426)%2B(I30%26gt%3B%24I%2426)%2B(J30%26gt%3B%24J%2426))%26gt%3B0%2C%22yes%22%2C%22%22)%3C%2FP%3E%3CP%3Eand%20can%20be%20copied%20down%20through%20I6%3AI23%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756511%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20make%20an%20array%20formula%20which%20select%20only%20information%20fitting%20certain%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756511%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUMPRODUCT(--(%24D28%3A%24I28%26gt%3BTRANSPOSE(%24D%249%3A%24D%2414)))%2C%20%22yes%22%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eassuming%20sequences%20are%20in%20same%20order%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758095%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20make%20an%20array%20formula%20which%20select%20only%20information%20fitting%20certain%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758095%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20message%2C%20the%20formula%20appears%20to%20work%20well%20for%20my%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758096%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20make%20an%20array%20formula%20which%20select%20only%20information%20fitting%20certain%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758096%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F200080%22%20target%3D%22_blank%22%3E%40Roger%20Govier%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20message.%20The%20formula%20has%20worked%20well%2C%20and%20if%20you%20are%20interested%20and%20array%20formula%20also%20works%20well%20for%20this%20scenario.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20time%2C%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758497%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20make%20an%20array%20formula%20which%20select%20only%20information%20fitting%20certain%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20your%20response.%3C%2FP%3E%0A%3CP%3EActually%2C%20both%20formulas%20are%20array%20formulas%20using%20SUMPRODUCT()%3C%2FP%3E%0A%3CP%3ESergei's%20solution%20is%20more%20elegant%20and%20the%20best.%3C%2FP%3E%3C%2FLINGO-BODY%3E
calof1
Contributor

Hi,

 

I have a large number of products and criteria. I wish to show the term "yes" for restriciiton if my result breaches one of my tolerance criteria. Due to the large number of criteria and products, i believe i need an index match array formula or similar. I have attached an example.

 

Can someone please assist.

 

Thank you kindly for your assistance.

 

5 Replies

@calof1 

I inserted a row at row 25

I then copied your rates from D9:D14 and Transposed them to D26:J26

(You are missing YEN from your list so I inserted that at row 12)

 

The formula in I5 then becomes

=IF(SUMPRODUCT(--(D30>$D$26)+(E30>$E$26)+(F30>$F$26)+(G30>$G$26)+(H30>$H$26)+(I30>$I$26)+(J30>$J$26))>0,"yes","")

and can be copied down through I6:I23

 

@calof1 

As variant that could be

=IF(SUMPRODUCT(--($D28:$I28>TRANSPOSE($D$9:$D$14))), "yes","")

assuming sequences are in same order

 

Solution

Hi @Sergei Baklan 

 

Thank you for the message, the formula appears to work well for my needs.

 

Much appreciated.

Hi@Roger Govier 

 

Thank you for the message. The formula has worked well, and if you are interested and array formula also works well for this scenario.

 

Thank you for your time, much appreciated.

@calof1 

Hi

Thank you for your response.

Actually, both formulas are array formulas using SUMPRODUCT()

Sergei's solution is more elegant and the best.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies