Year to date calculation

[BeckyLH88]

Hello all,

I have this formula that works to pick up the month of the current date but I also now need to incorporate dates before this to give me a YTD.

Here's my month formula:

=VLOOKUP($B7,'[Budget FY2024 - V1.xlsx] Budget'!$B$3:$N$132,MATCH(E$3,'[Budget FY2024 - V1.xlsx] Budget'!$B$3:$N$3,1),0)

B7 = the expenditure time
B3 - N132 is my data range to sum
E3 is my date
B3 - N3 is my date range.

I am quite sure this will be an easy solve for someone who hasn't been trying to do this for a few hours

Appreciate your help in advance, thank you!

 

[alansidman]

Suggest you provide a sample worksheet with XL2BB (No pictures as we cannot manipulate data in a picture) and a mocked up solution of what you desire. 8-10 records should suffice. This should expedite a workable solution for you.

 

[awoohaw]

Although @alansidman is correct in that a copy of your data would help. I have questions just by looking at your post. And maybe you're assuming the forum will determine what you really mean but please answer this:

B7 = the expenditure time

Please explain how this value can also be in range B3:N132? is the entire range a bunch of expediture times? if so, why are the column headers (my guess at this) dates and not times?

B3 - N132 is my data range to sum

This range consumes ALL of the other three cell references that you're describing here? (B7, E3, and B3:N3), so this is very ambiguous.

E3 is my date

You describe the values in B3:N132 as data, yet here you are saying part of the data is a DATE.

B3 - N3 is my date range.

Likewise, same as E3 being a date, you say this entire range is data in B3:N132

So, please clarify if you cannot post a xl2bb or table of your data.
Is Row 3 (cells C3:N:3) actually your column headers, and are they monthend/begining dates (or some date interval of 12 regular periods)?
Is Column B (cells (B4:B132) row headers? Could it be a name(/category/vendor etc?) or is a time value (if so what time interval)?
Is the range C4:N132 a data range? And what kind of data is it? Is it numeric, or text?

Lastly, I can't figure out what you mean by "E3 is my date", is this the date you're trying to get summarized data for?
What are some expected results for a specific target date?

 

[BeckyLH88]

Suggest you provide a sample worksheet with XL2BB (No pictures as we cannot manipulate data in a picture) and a mocked up solution of what you desire. 8-10 records should suffice. This should expedite a workable s

Ive tried to upload but unfortunately getting an error. I'll try another laptop when I can.

 

[BeckyLH88]

Although @alansidman is correct in that a copy of your data would help. I have questions just by looking at your post. And maybe you're assuming the forum will determine what you really mean but please answer this:


Please explain how this value can also be in range B3:N132? is the entire range a bunch of expediture times? if so, why are the column headers (my guess at this) dates and not times?


This range consumes ALL of the other three cell references that you're describing here? (B7, E3, and B3:N3), so this is very ambiguous.


You describe the values in B3:N132 as data, yet here you are saying part of the data is a DATE.


Likewise, same as E3 being a date, you say this entire range is data in B3:N132

So, please clarify if you cannot post a xl2bb or table of your data.
Is Row 3 (cells C3:N:3) actually your column headers, and are they monthend/begining dates (or some date interval of 12 regular periods)?
Is Column B (cells (B4:B132) row headers? Could it be a name(/category/vendor etc?) or is a time value (if so what time interval)?
Is the range C4:N132 a data range? And what kind of data is it? Is it numeric, or text?

Lastly, I can't figure out what you mean by "E3 is my date", is this the date you're trying to get summarized data for?
What are some expected results for a specific target date?

B3 to n132 is my data of which I'm using to return a value. (
Data sheet)

C3 to N3 to my dates, 01/04/23, 01/05/23 and so on until 01/03/24 (data sheet)

E3 is on another sheet but it's the date to look up from such as 01/04/23, on a list so I can change each month. (Criteria)

Sorry appreciate its difficult without being uploaded data.

My purpose now is to do a formula that says

Sum the range if it's between 01/04/2023 and 01/10/23 and matches the income or expenditure type for example staff costs.

I tried a simple sumifs but I was getting a Value error. I think it's a simple fix, previously I've done this using sumifs, index and match but I'm sure there is a better way to do it

 

[BeckyLH88]

B7 is on my summary sheet such as staff costs, so it's my criteria

 

[Alex Blakenburg]

Something like this might work for you:
(entered into Row 7)
PS: It is normally not a good idea to have a leading space on a sheet name which you appear to have before the word Budget.
Note: The filter function will work on a closed workbook. I don't believe Sumif(s) will.

=SUM(FILTER(FILTER('[Budget FY2024 - V1.xlsx] Budget'!$C$4:$N$10,'[Budget FY2024 - V1.xlsx] Budget'!$C$3:$N$3<=E3),'[Budget FY2024 - V1.xlsx] Budget'!$B$4:$B$10=$B7))

I normally find it easier to read if it entered something like this:

=SUM(
       FILTER(
            FILTER('[Budget FY2024 - V1.xlsx] Budget'!$C$4:$N$10,
                         '[Budget FY2024 - V1.xlsx] Budget'!$C$3:$N$3<=E3),
            '[Budget FY2024 - V1.xlsx] Budget'!$B$4:$B$10=$B7)
)

In case it helps anyone else my Test Data for the Budget sheet was:

Budget FY2024 - V1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3			1/04/2023	1/05/2023	1/06/2023	1/07/2023	1/08/2023	1/09/2023	1/10/2023	1/11/2023	1/12/2023	1/01/2024	1/02/2024	1/03/2024
4		Postage	2	3	2	3	1	1	1	1	1	1	1	1
5		Staff Costs	20	30	20	30	10	10	10	10	10	10	10	10
6		Travel	200	300	200	300	100	100	100	100	100	100	100	100
Budget

My summary sheet

20231112 Closed workbook YTD BeckyLH88.xlsx
	A	B	C	D	E
1
2
3				Period	1/08/2023
4
5
6		Description	YTD
7		staff costs	110
Summary

Cell Formulas
Range	Formula
C7	C7	=SUM(FILTER(FILTER('[Budget FY2024 - V1.xlsx] Budget'!$C$4:$N$10,'[Budget FY2024 - V1.xlsx] Budget'!$C$3:$N$3<=E3),'[Budget FY2024 - V1.xlsx] Budget'!$B$4:$B$10=$B7))

 

[BeckyLH88]

Thank you, you are an amazing person Alex! Thank you for taking the time to show me this, it's fixed my issue and saved me quite a lot of time!!!

 

[Alex Blakenburg]

Appreciate the feedback. It was definitely a team effort, we would not have got there that quickly without @awoohaw's questions and your comprehensive response.