Sum between dates

[karldugan]

Hi all,

I have the below table - The bit on the right I can't change as it comes out of our system like this. There will only ever be the 6 date rows and 3 corresponding values, so don't mind a formula that repeats and adds each part together. I tried sumifs but couldn't get that to work.

I've manually calculated wc 18/07/2023, which is value 2 and 3 in column 1 and value 1 in column 2 - I feel like it's a sumproduct but I'm not very good with those.

Thanks,
Karl.

W/C	Total Accrual		Value	1​	2​	3​
10/07/2023​	£2,282.13​		Accrual Start Value 1	01/07/2022​	01/07/2023​	01/07/2024​
17/07/2023​			Accrual Stop Value 1	30/06/2023​	30/06/2024​	30/06/2025​
24/07/2023​			Accrual Start Value 2	01/01/2023​	01/01/2024​	01/01/2025​
31/07/2023​			Accrual Stop Value 2	31/12/2023​	31/12/2024​	31/12/2025​
07/08/2023​			Accrual Start Value 3	01/12/2022​	01/12/2023​	01/12/2024​
14/08/2023​			Accrual Stop Value 3	31/12/2023​	31/12/2024​	31/12/2025​
21/08/2023​			Value 1	£594.52​	£805.48​	£2,534.08​
28/08/2023​			Value 2	£671.17​	£3,457.21​	-£1,159.11​
04/09/2023​			Value 3	£805.48​	£2,534.08​	£1,954.53​
11/09/2023​
18/09/2023​
25/09/2023​
02/10/2023​
09/10/2023​
16/10/2023​
23/10/2023​
30/10/2023​
06/11/2023​
13/11/2023​
20/11/2023​
27/11/2023​

 

[OaklandJim]

I can't promise that I can do this but I'll try. If not me then possibly other people who are smarter about Excel.

Tell us why the values (value 2 and 3 in column 1 and value 1 in column 2) used for the sum for 10/07/2023 are identified.

If the data is not confidential hopefully you could post a link to your workbook. Or you can enter fake-but-realistic data before providing the link. Put the file on Dropbox, Box, 1Drive, Google Drive etc. Use the link icon above the message area.

The worst case is that you have a custom function (called a User Defined Function or UDF) built using VBA that does what is needed, if worksheet formulas do not work.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)