Adding a date

%3CLINGO-SUB%20id%3D%22lingo-sub-3192258%22%20slang%3D%22en-US%22%3EAdding%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192258%22%20slang%3D%22en-US%22%3E%3CP%3EWas%20wondering%20if%20there%20was%20a%20way%20to%20add%20a%20date.%20So%20lets%20say%20I%20don't%20want%20it%20to%20generate%20a%20sum%20unless%20it%20is%20for%20a%20specific%20date%20or%20for%20specific%20days%20in%20the%20week.%20Is%20it%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3192258%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192344%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192344%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313378%22%20target%3D%22_blank%22%3E%40Matthew_morales%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20dates%20in%20A2%3AA100%20and%20corresponding%20values%20in%20B2%3AB100.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20To%20sum%20the%20values%20for%20a%20specific%20date%3A%3C%2FP%3E%0A%3CP%3EEnter%20that%20date%20in%20D2.%3C%2FP%3E%0A%3CP%3EIn%20E2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS(B2%3AB100%2C%20A2%3AA100%2C%20D2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20To%20sum%20values%20for%20a%20range%20of%20dates%3A%3C%2FP%3E%0A%3CP%3EEnter%20the%20first%20date%20to%20include%20in%20D2%20and%20the%20last%20date%20in%20E2.%3C%2FP%3E%0A%3CP%3EIn%20F2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS(B2%3AB100%2C%20A2%3AA100%2C%20%22%26gt%3B%3D%22%26amp%3BD2%2C%20A2%3AA100%2C%20%22%26lt%3B%3D%22%26amp%3BE2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3)%20To%20sum%20values%20for%20a%20specific%20day%20of%20the%20week%3A%3C%2FP%3E%0A%3CP%3EEnter%20the%20number%20of%20the%20weekday%20in%20D2%2C%20with%201%3DSunday%2C%202%3DMonday%2C%20etc.%3C%2FP%3E%0A%3CP%3EIn%20E2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(B2%3AB100%2C--(WEEKDAY(A2%3AA100)%3DD2))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4)%20To%20sum%20values%20for%20a%20a%20range%20of%20days%20of%20the%20week%3A%3C%2FP%3E%0A%3CP%3EEnter%20the%20number%20of%20the%20first%20weekday%20to%20include%20in%20D2%2C%20and%20that%20of%20the%20last%20weekday%20in%20E2%2C%20with%201%3DSunday%2C%202%3DMonday%2C%20etc.%3C%2FP%3E%0A%3CP%3EIn%20F2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(B2%3AB100%2C(WEEKDAY(A2%3AA100)%26gt%3B%3DD2)*(WEEKDAY(A2%3AA100)%26lt%3B%3DE2))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Was wondering if there was a way to add a date. So lets say I don't want it to generate a sum unless it is for a specific date or for specific days in the week. Is it possible?

1 Reply

@Matthew_morales 

Let's say you have dates in A2:A100 and corresponding values in B2:B100.

 

1) To sum the values for a specific date:

Enter that date in D2.

In E2, enter the formula

 

=SUMIFS(B2:B100, A2:A100, D2)

 

2) To sum values for a range of dates:

Enter the first date to include in D2 and the last date in E2.

In F2, enter the formula

 

=SUMIFS(B2:B100, A2:A100, ">="&D2, A2:A100, "<="&E2)

 

3) To sum values for a specific day of the week:

Enter the number of the weekday in D2, with 1=Sunday, 2=Monday, etc.

In E2, enter the formula

 

=SUMPRODUCT(B2:B100,--(WEEKDAY(A2:A100)=D2))

 

4) To sum values for a a range of days of the week:

Enter the number of the first weekday to include in D2, and that of the last weekday in E2, with 1=Sunday, 2=Monday, etc.

In F2, enter the formula

 

=SUMPRODUCT(B2:B100,(WEEKDAY(A2:A100)>=D2)*(WEEKDAY(A2:A100)<=E2))