Formula to pull data when multiple criteria are met.

Good Day all,

I have a formula that pulls data when a specific criteria is met,

however i would like to modify the formula to pull data if 2 criteria are met.

here is what i havew now:=IFERROR(1/(1/SUMIFS('MATERIAL DATA'!$K$5:$K$1994,'MATERIAL DATA'!$L$5:$L$1994,BOM!$K5)),"")

so this asks sum sheet called material data k5 to 5 1994 if sheet called material data l5 to I1994 matches sheet called BOM k5

this works perfecty however i would like to add a second match . and i am not sure where i would put it in the formula to have it work.

the added part to the formula is: also search sheet called material data in cells w5:w1994 for "MF"or"CS" if the search matches MF then sum only the ones that match MF if the search matches CS then sum only the ones that match CS.

i hope ive made it clear enough.

if not let me know

thanks for any help

mike rand

What if there is "MF" and "CS" in column W?

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

hello,

attached is the excel book in question

note: sheet BOM in blue needs to calculate only if sheet material data cell w4:w1994 contains MF as well as the other
criteria in the formula

note: sheet BOM in yellow needs to calculate only if sheet material data cell w4:w1994 contains CS as well as the other criteria in the formula


thanks

That's not a small representative sample and the desired results are not there...

Also, these do not have to be entered as array formulas.

Try these:

L4 =IFERROR(1/(1/SUMIFS('MATERIAL DATA'!K:K,'MATERIAL DATA'!L:L,K4,'MATERIAL DATA'!W:W,"MF")),"")

AJ4 =IFERROR(1/(1/SUMIFS('MATERIAL DATA'!K:K,'MATERIAL DATA'!L:L,K4,'MATERIAL DATA'!W:W,"CS")),"")

Yes your right ,

however i tried to make it smaller but each time i did it stopped creating data and all the squares went to errors so there would have been no data for you to see , sorry


thanks i will try those formulas and omit the control shift enter

thanks again
i will let you know how it turned out or if i have any problems.

mike rand

Falcondude,



PEFECT


THANKS SO MUCH,


works lieka charm.

mike rand

Happy to help. Thanks for the rep!

Falcondude,

I am having troubles converting the formula for the GORES section on the BOM sheet

formula you modified works perfect for all other but i can seem to get the right modification for the gores.

you will see on BOM cell at6 i have this formula,

=IFERROR(INDEX('MATERIAL DATA'!$G$5:$G$1994,SMALL(IF('MATERIAL DATA'!$T$5:$T$1994="GORE",ROW('MATERIAL DATA'!$T$5:$T$1994)),ROWS($1:3))),"")

i need it to only work if 'MATERIAL DATA'!$T$5:$T$1994="GORE" is correct and if 'MATERIAL DATA'!$q$5:$q$1994="ss" is correct,

can you help me out on this as well.

thanks again

mike rand

That is not the formula that you have in BOM!AT6. That is the formula that you have in BOM!AT7.

You can use this in AT6:

=IFERROR(INDEX('MATERIAL DATA'!G:G,SMALL(IF(('MATERIAL DATA'!Q$5:Q$1994="ss")*('MATERIAL DATA'!T$5:T$1994="GORE"),ROW('MATERIAL DATA'!$T$5:$T$1994)),ROWS($1:1))),"") Ctrl Shift Enter