SOLVED

# Help with what formula to use

Copper Contributor

# Help with what formula to use

Hello everyone

I need help from this community.

I have table with data like that.

Column A - there are dates from 1.1. 2023  till today and every day new day is added

Comun B, C, D - there are diferent numbers like value, quantity etc...

My ideal set is that I have 2 scroll down list where I will pick up starting date and ending date. And formula will sum up all number from column B, C or D between those two picked dates.

Is it possible to do ??

2 Replies
best response confirmed by Spanek1740 (Copper Contributor)
Solution

# Re: Help with what formula to use

it is possible to achieve your desired functionality using Excel formulas. You can use the SUMIFS function to sum values in a range based on multiple criteria, in this case, the date range selected from the drop-down lists.

Here's how you can set it up:

1. Create two drop-down lists for selecting the start date and end date. You can use Data Validation to create these lists. Let's say these drop-down lists are located in cells F1 and G1, respectively.
2. In cell H1, you can use the following formula to sum values in column B between the selected start and end dates:

SUMIFS(B:B, A:A, ">="&F1, A:A, "<="&G1)

This formula sums values in column B (change B:B to C:C for column C, and D:D for column D) where the date in column A is greater than or equal to the start date (F1) and less than or equal to the end date (G1).

3. Drag or copy the formula in cell H1 to cells I1 and J1 to calculate the sums for columns C and D, respectively.

Now, when you select the start date and end date from the drop-down lists in cells F1 and G1, the sums for the selected date range will be calculated in cells H1, I1, and J1 for columns B, C, and D, respectively.

Remember to adjust the cell references and ranges as needed based on your actual data layout. Also, make sure your dates in column A are formatted as dates, not text. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

# Re: Help with what formula to use

Checkout the solution provided using Filter function in attached worksheet.

1 best response

Accepted Solutions
best response confirmed by Spanek1740 (Copper Contributor)
Solution

# Re: Help with what formula to use

it is possible to achieve your desired functionality using Excel formulas. You can use the SUMIFS function to sum values in a range based on multiple criteria, in this case, the date range selected from the drop-down lists.

Here's how you can set it up:

1. Create two drop-down lists for selecting the start date and end date. You can use Data Validation to create these lists. Let's say these drop-down lists are located in cells F1 and G1, respectively.
2. In cell H1, you can use the following formula to sum values in column B between the selected start and end dates:

SUMIFS(B:B, A:A, ">="&F1, A:A, "<="&G1)

This formula sums values in column B (change B:B to C:C for column C, and D:D for column D) where the date in column A is greater than or equal to the start date (F1) and less than or equal to the end date (G1).

3. Drag or copy the formula in cell H1 to cells I1 and J1 to calculate the sums for columns C and D, respectively.

Now, when you select the start date and end date from the drop-down lists in cells F1 and G1, the sums for the selected date range will be calculated in cells H1, I1, and J1 for columns B, C, and D, respectively.

Remember to adjust the cell references and ranges as needed based on your actual data layout. Also, make sure your dates in column A are formatted as dates, not text. The text was created with the help of AI.

My answers are voluntary and without guarantee!