SOLVED

Can you help me create the right formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-2855452%22%20slang%3D%22en-US%22%3ECan%20you%20help%20me%20create%20the%20right%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2855452%22%20slang%3D%22en-US%22%3EI%20have%20a%20shared%20spreadsheet%20that%20I%20need%20to%20pull%20last%20weeks%20data%20from%20every%20week%20for%20a%20report.%20I%20think%20I%20need%20to%20use%20a%20combination%20of%20count%20if%20and%20vlookup%20or%20the%20new%20xlookup%20and%20hoping%20someone%20can%20help.%20For%20examples%20sake%20we%E2%80%99ll%20say%20I%20have%20data%20in%20column%20A%20%3D%20MM%2FDD%2FYY%20and%20column%20B%20%3D%20status%20with%203%20choices.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%20to%20be%20able%20to%20enter%20a%20date%20frame%20(usually%20mon-sun%20of%20the%20previous%20week)%20and%20have%20the%20formula%20look%20at%20column%20A%20to%20find%20if%20a%20row%20fell%20within%20that%20date%20range%2C%20and%20if%20so%2C%20check%20column%20B%20for%20a%20particular%20status%20(kicked%20off)%20and%20total%20how%20many%20items%20were%20within%20the%20date%20frame%20with%20the%20status%20%E2%80%9Ckicked%20off%E2%80%9D.%3CBR%20%2F%3E%3CBR%20%2F%3EHelp%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2855452%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-2855522%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20help%20me%20create%20the%20right%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2855522%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1188227%22%20target%3D%22_blank%22%3E%40Getwiser%3C%2FA%3E%26nbsp%3BCreated%20a%20simplified%20example%20in%20the%20attached%20file.%20since%20you%20considered%20using%20XLOOKUP%2C%20i%20suspect%20you%20are%20on%20a%20modern%20Excel%20version.%20Then%20you%20can%20use%20FILTER%20to%20achieve%20what%20you%20want.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2855596%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20help%20me%20create%20the%20right%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2855596%22%20slang%3D%22en-US%22%3EYES!!!%20It%20took%20me%20a%20minute%20to%20tweak%20it%20because%20I%20was%20pulling%20from%20a%20different%20page%2C%20but%20I%20got%20it%20to%20work!%20Thank%20you!%3C%2FLINGO-BODY%3E
New Contributor
I have a shared spreadsheet that I need to pull last weeks data from every week for a report. I think I need to use a combination of count if and vlookup or the new xlookup and hoping someone can help. For examples sake we’ll say I have data in column A = MM/DD/YY and column B = status with 3 choices.

I need to be able to enter a date frame (usually mon-sun of the previous week) and have the formula look at column A to find if a row fell within that date range, and if so, check column B for a particular status (kicked off) and total how many items were within the date frame with the status “kicked off”.

Help?
2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Getwiser Created a simplified example in the attached file. Since you considered using XLOOKUP, I suspect you are on a modern Excel version. Then you can use FILTER to achieve what you want. See attached.

YES!!! It took me a minute to tweak it because I was pulling from a different page, but I got it to work! Thank you!