Help - Dynamically Create an Array, Based on Key Cell, and Count Text within Array

Hello everyone,

First post here. I need some assistance with writing a formula that will allow me to count text values within an array of cells, creating the array based on the a key cell being present in a specific column. This spreadsheet is actually built in Google Drive, but most of (if not all) of the Excel functions carry over. Hoping y'all can help me out, pretending that this is built in Excel.

I have a calendar I built, listed by week. I want to be able to count the number of times a particular text value shows up in each week, based on the week. I want to be able to do 1, 2, or 3 week's worth of counting, and sum each week's counts together. This may be an odd use case, but I'm trying to build a slick meal planning tool for my wife so she can meal plan for our family. We're on a tight budget, so we're trying to make every dollar count. Here's a screenshot to help.

Picture1.jpg

You can see at the top of the sheet, three cells that allow me to select the Sunday that starts each week of the up to 3 weeks of meal planning. Below that, you'll see an example of what the week looks like with the weeks starting on Sunday, in column B. There are different meal options, controlled by data validation basically making them drop-down fields. Below this week are additional weeks, carrying on for an entire calendar year. Yes, we're doing this all for meal planning. I'm really good at over-engineering things and making them complicated :-)

Basically, what I'm trying to do, logically, is query B4:B389 for the value in cell B2 and, if found, count the number of times the text value in a reference cell (the meal name, present in a different sheet) is present in an array where the upper left-most cell in the array is the cell containing the date of the Sunday starting the week.

So, if I'm planning for the week of 4/30/2017, the formula would query B4:B389, find the value of 4/30/2017 in B124, create an array with B124 being the left-most upper cell in the array, and count the number of times the text value in the reference cell is present in this array, dynamically created based off of the date value of the Sunday. The array for this week would B124:H129.

Then, add this count to the output of this same formula, just referencing C2, and referencing D2, creating the same dynamic row/column array, thus allowing for 1, 2, or 3 weeks of meal planning.

Conceptually, I could do this by nesting 52 IF(DATEVALUE(COUNTIF functions (or something to that likeness, which I've done before) for each of the three date options (cells B2, C2, D2), but I'd then have 156 nested formula sets in 1 cell, for each meal name, and that seems rather clunky.

Thanks in advance for the help!

Cheers,

Matt

Hi Matt- Welcome to ExcelForum! It would be a big help if you could attach a sample workbook to your post, pared down to essentials, so we can try things out. You'll probably get more responses by doing so. Just click Edit Post, then hit Go Advanced. Scroll down to Manage Attachments and click. Browse for your file and upload it. Thanks- Lee

Ok, I just took an extra 15 minutes of my life to re-create your workbook (just sayin'... ). Your .jpg showed cell E1 selected, so I put the following formula there, but you can move it anywhere on the same sheet as the weekly entries. You'll need to adjust the reference to your off-sheet menu item. (I used Sheet2! A1). All this is shown in the attached file. I hope you find it useful. - Lee

Formula:

Please Login or Register  to view this content.

ps. If Excel seems to want cell E1 to be a date, just change its number format to 'General'.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Hey, got the demo. Here's the updated version. Bon appetit...

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee