SOLVED

Formula to automatically move to a new column based on date.

%3CLINGO-SUB%20id%3D%22lingo-sub-1740408%22%20slang%3D%22en-US%22%3EFormula%20to%20automatically%20move%20to%20a%20new%20column%20based%20on%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740408%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%20attached%20is%20an%20example%20of%20the%20data%20I'm%20working%20with.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20having%20trouble%20figuring%20out%20is%20a%20formula%20that%20will%20move%20over%20automatically%20as%20the%20date%20changes%20with%20the%20%3DTODAY().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20use%20%3DSUMIF()%20to%20provide%20a%20total%20of%20the%20range%20under%20the%20date.%20But%20I%20need%20to%20shift%20over%20the%20formula's%20criteria%20references%20every%20day.%20This%20formula%20will%20help%20with%20other%20parts%20of%20some%20work%20as%20well%20and%20would%20go%20a%20much%20longer%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22869%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2271%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2271%22%3ECategory%3C%2FTD%3E%3CTD%20width%3D%2271%22%3E10%2F1%2F2020%3C%2FTD%3E%3CTD%20width%3D%2271%22%3E10%2F2%2F2020%3C%2FTD%3E%3CTD%20width%3D%2271%22%3E10%2F3%2F2020%3C%2FTD%3E%3CTD%20width%3D%2271%22%3E10%2F4%2F2020%3C%2FTD%3E%3CTD%20width%3D%2271%22%3E10%2F5%2F2020%3C%2FTD%3E%3CTD%20width%3D%2271%22%3E10%2F6%2F2020%3C%2FTD%3E%3CTD%20width%3D%2281%22%3E10%2F7%2F2020%3C%2FTD%3E%3CTD%20width%3D%2271%22%3E10%2F8%2F2020%3C%2FTD%3E%3CTD%20width%3D%2271%22%3E10%2F9%2F2020%3C%2FTD%3E%3CTD%20width%3D%2278%22%3E10%2F10%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBlack%3C%2FTD%3E%3CTD%3EDark%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E44%3C%2FTD%3E%3CTD%3E32%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E22%3C%2FTD%3E%3CTD%3E150%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E38%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1740408%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-1740435%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20automatically%20move%20to%20a%20new%20column%20based%20on%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740435%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F154670%22%20target%3D%22_blank%22%3E%40Robert%20Grayson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20explain%20in%20detail%20what%20you%20actually%20want%20to%20do%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740466%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20automatically%20move%20to%20a%20new%20column%20based%20on%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20have%20Today()%20on%20A1%2C%20it%20produces%20today's%20current%20date.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20a%20table%2C%20I%20have%20a%20set%20of%20data%20with%20dates%20as%20headers%20and%20numeric%20data%20under%20each%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20figuring%20out%20an%20appropriate%20formula%20to%20automatically%20sum%20data%20based%20on%20the%20date%20in%20the%20header.%20And%20that%20the%20date%20in%20the%20header%20needs%20to%20match%20the%20date%20in%20A1%20(%22TODAY()%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20using%20a%20SUMIF%20Formula%20to%20Sum%20based%20on%20other%20criteria%2C%20but%20I%20have%20to%20adjust%20range%20being%20referenced%20in%20the%20data%20to%20reflect%20the%20data%20in%20the%20column%20under%20the%20present%20day%20represented%20in%20the%20header.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20makes%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740556%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20automatically%20move%20to%20a%20new%20column%20based%20on%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740556%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F154670%22%20target%3D%22_blank%22%3E%40Robert%20Grayson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks.%20Use%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUM(INDEX(C5%3AL10%2C0%2CMATCH(A1%2CC4%3AL4%2C0)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740559%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20automatically%20move%20to%20a%20new%20column%20based%20on%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740559%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F154670%22%20target%3D%22_blank%22%3E%40Robert%20Grayson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlternatively%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((C4%3AL4%3DA1)*C5%3AL10)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740634%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20automatically%20move%20to%20a%20new%20column%20based%20on%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F154670%22%20target%3D%22_blank%22%3E%40Robert%20Grayson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3EI%20think%20you%20should%20follow%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(FILTER(%24C%244%3A%24L%2410%2C%24C%244%3A%24L%244%3D%24P%243)*((%24B%244%3A%24B%2410%3DO4)))%3C%2FP%3E%3CP%3E%3DSUM(FILTER(%24C%244%3A%24L%2410%2C%24C%244%3A%24L%244%3D%24P%243)*((%24B%244%3A%24B%2410%3DO5)))%3C%2FP%3E%3CP%3EMy%20solution%20file%20is%20attached%20to%20this%20message.%3C%2FP%3E%3CP%3EGood%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello everyone, attached is an example of the data I'm working with. 

 

What I'm having trouble figuring out is a formula that will move over automatically as the date changes with the =TODAY().

 

I currently use =SUMIF() to provide a total of the range under the date. But I need to shift over the formula's criteria references every day. This formula will help with other parts of some work as well and would go a much longer well.

 

NameCategory10/1/202010/2/202010/3/202010/4/202010/5/202010/6/202010/7/202010/8/202010/9/202010/10/2020
BlackDark50344320221502380



Thank you,

7 Replies
Highlighted

@RobG92 

Can you explain in detail what you actually want to do?

Highlighted

@Hans Vogelaar 

 

If I have Today() on A1, it produces today's current date.

In a table, I have a set of data with dates as headers and numeric data under each date.

 

I need help figuring out an appropriate formula to automatically sum data based on the date in the header. And that the date in the header needs to match the date in A1 ("TODAY()").

 

I was using a SUMIF Formula to Sum based on other criteria, but I have to adjust range being referenced in the data to reflect the data in the column under the present day represented in the header. 

 

Hope that makes sense.

Highlighted

@RobG92 

Thanks. Use:

 

=SUM(INDEX(C5:L10,0,MATCH(A1,C4:L4,0)))

Highlighted

@RobG92 

Alternatively:

 

=SUMPRODUCT((C4:L4=A1)*C5:L10)

Highlighted

@RobG92 

Hi there,

I think you should follow the following formula:

 

=SUM(FILTER($C$4:$L$10,$C$4:$L$4=$P$3)*(($B$4:$B$10=O4)))

=SUM(FILTER($C$4:$L$10,$C$4:$L$4=$P$3)*(($B$4:$B$10=O5)))

My solution file is attached to this message.

Good luck.

 

 

Highlighted

@Hans Vogelaar 

 

I like this formula, how would I modify this formula for a third and fourth category to sort?

Example from the sheet attached.

I have another set of data where, out of the sum collected with your formula, I need the singular sum total of items that correspond to a certain language and/or type. Which is why I used SumIF

 

Highlighted
Best Response confirmed by RobG92 (Occasional Contributor)
Solution

@RobG92 

For example for US on the specified date:

 

=SUMPRODUCT(B4:C10*(B3:C3=A1)*(F4:F10="US"))

 

See the attached version.