Look up two columns, return the second column with a sum of counts for both columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2712314%22%20slang%3D%22en-US%22%3ELook%20up%20two%20columns%2C%20return%20the%20second%20column%20with%20a%20sum%20of%20counts%20for%20both%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712314%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20going%20to%20try%20to%20explain%20this%20to%20the%20best%20of%20my%20ability%2C%20please%20reach%20out%20if%20anything%20seems%20confusing.%20In%20column%20A%20(labeled%20Internal%20A)%2C%20I%20have%20a%20list%20of%20dates%20that%20are%20Sundays.%20In%20column%20B%2C%20I%20have%20counts%20of%20numbers%20associated%20with%20that%20specific%20Sunday%20date.%20In%20Column%20D%2C%20I%20have%20Monday%20dates%20(except%20for%20the%20holiday%20which%20is%20on%20a%20Tuesday)%20that%20are%20labeled%20(External%20B)%20and%20I%20have%20counts%20associated%20with%20that%20date%20in%20Column%20E.%20In%20Column%20N%20(Final%20Date)%20are%20the%20results%20that%20I%20desire%20and%20the%20counts%20Column%20O%20associated%20with%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20my%20end%20result%20to%20be%20in%20Columns%20N%20and%20O%20are%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20a%20list%20of%20all%20of%20the%20internal%20dates%20%2B1%20and%20a%20list%20of%20the%20external%20dates%20as%20well.%20For%20example%2C%20Cell%20A11%20has%20a%20date%20of%2005%2F23%2F2021%20with%20a%20count%20of%2086.%20In%20column%20H%2C%20I%20would%20like%20to%20add%20a%20%2B1%20to%20that%2005%2F23%2F2021%20date%20(Cell%20A11)%20which%20would%20make%20it%2005%2F24%2F2021%20which%20is%20already%20in%20Cell%20D3%20for%20the%20external%20date.%20For%20the%20final%20total%20count%20(column%20N)%20it%20would%20be%2005%2F24%2F2021%20with%20a%20count%20of%2090%20in%20Column%20O%20(86%20for%2005%2F23%20%2B%204%20for%2005%2F24).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20that%20I%20have%20is%20highlighted.%20On%20Column%20D%2C%20Cell%20D10%2C%20there%20is%20a%20date%200f%2009%2F27%2F2021%20and%20b%2Fc%20there%20is%20no%2009%2F26%2F2021%20on%20Column%20A%2C%20it%20would%20not%20populate%20with%20the%20formula%20that%20I%20am%20using.%20I%20would%20like%20to%20still%20use%20that%20date%2C%20even%20if%20it%20the%20previous%20date%20is%20not%20in%20Column%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*Below%20is%20a%20bonus%20if%20that%20is%20possible*%20If%20the%20date%20is%20a%20holiday%2007%2F04%2F2021%2C%20I%20would%20like%20it%20to%20go%20to%20the%20next%20date%20of%2007%2F06%2F2021%20as%20a%20final%20date%20and%20count%20of%204.%20So%20the%20final%20date%20would%20be%2007%2F06%2F2021%20with%20a%20count%20of%204%20(1%20from%2007%2F04%20and%203%20from%2007%2F06).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20greatly%20appreciated.%20Please%20see%20attachment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2712314%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hello, I am going to try to explain this to the best of my ability, please reach out if anything seems confusing. In column A (labeled Internal A), I have a list of dates that are Sundays. In column B, I have counts of numbers associated with that specific Sunday date. In Column D, I have Monday dates (except for the holiday which is on a Tuesday) that are labeled (External B) and I have counts associated with that date in Column E. In Column N (Final Date) are the results that I desire and the counts Column O associated with them.

 

What I would like my end result to be in Columns N and O are the following:

 

I would like to have a list of all of the internal dates +1 and a list of the external dates as well. For example, Cell A11 has a date of 05/23/2021 with a count of 86. In column H, I would like to add a +1 to that 05/23/2021 date (Cell A11) which would make it 05/24/2021 which is already in Cell D3 for the external date. For the final total count (column N) it would be 05/24/2021 with a count of 90 in Column O (86 for 05/23 + 4 for 05/24).

 

The problem that I have is highlighted. On Column D, Cell D10, there is a date 0f 09/27/2021 and b/c there is no 09/26/2021 on Column A, it would not populate with the formula that I am using. I would like to still use that date, even if it the previous date is not in Column A.

 

*Below is a bonus if that is possible* If the date is a holiday 07/04/2021, I would like it to go to the next date of 07/06/2021 as a final date and count of 4. So the final date would be 07/06/2021 with a count of 4 (1 from 07/04 and 3 from 07/06).

 

Any assistance would be greatly appreciated. Please see attachment.

0 Replies