Share via

formula / work around to pick up data from last year and this year

Anonymous
2025-04-22T11:16:30+00:00

Would be looking for 2 formulas

The first formula would be to look at Material but only calculate the the ones that were ordered in 2023 but completed in 2024 and show the value combined

the second I don't know if may be the same but would be looking to see anything that is outstanding and pull the total value across so if something was ordered in 2023, but still hasn't been completed i want to know what value it is combined, then when it is completed that value would then decrease.

Name Value Blank Blank Blank Blank Blank Blank Blank Material Blank Blank Blank Date Created Blank Blank Blank Blank Date Completed
Alpha £100.00 N/A 15/05/2023 Comp 17/05/2023
Beta £50,000.00 Bronze 16/06/2023 Comp 26/06/2023
Beta £50,000.00 Gold 16/06/2023 Comp 26/06/2023
Omega £1,000.00 Silver 23/06/2023 Comp 29/06/2023
Omega £1,000.00 N/A 23/06/2023 Comp 29/06/2023
Alpha £22,500.00 Silver 29/06/2023 Comp 30/06/2023
Alpha £22,500.00 Silver 29/06/2023 Comp 30/06/2023
Omega £877.00 N/A 07/07/2023 Comp 07/07/2023
Alpha £100.00 Gold 17/02/2023 Comp 22/08/2023
Alpha £100.00 Silver 15/12/2023 Comp 17/04/2024
Omega £1,000.00 Bronze 25/04/2024 Comp 29/04/2024
Omega £877.00 Bronze 07/03/2024 Comp 18/05/2024
Omega £877.00 Bronze 07/07/2023 Comp 07/07/2024
Alpha £22,500.00 Bronze 29/06/2023 Outs
Beta £50,000.00 N/A 16/06/2023 Outs
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-04-23T09:50:10+00:00

    (if I have understood you well)

    For the first request in cell U1 apply this formula:

    =VSTACK(A1:S1, FILTER(A2:S20, (YEAR(N2:N20) = 2023) * (YEAR(S2:S20) = 2024)))

    or alternatively you can use this formula:

    =LET(gb, GROUPBY(HSTACK(A1:.A20, B1:.I20, J1:.J20, Q1:.Q20), IFERROR(HSTACK(N1:.N20, S1:.S20), ""), ARRAYTOTEXT, 3, 0,, IFERROR((YEAR(N2:.N20) = 2023) * (YEAR(S2:.S20) = 2024), 0)), IF(ISNUMBER(--gb), --gb, gb))

    For the second request in cell U6 apply this formula:

    =VSTACK(A1:S1, FILTER(A2:S20, (YEAR(N2:N20) = 2023) * (S2:S20 = "")))

    or alternatively you can use this formula:

    =LET(gb, GROUPBY(HSTACK(A1:.A20, B1:.I20, J1:.J20, Q1:.Q20), IFERROR(HSTACK(N1:.N20, S1:.S20), ""), ARRAYTOTEXT, 3, 0,, IFERROR((YEAR(N1:.N20) = 2023) * (S1:.S20 = ""), 1)), IF(ISNUMBER(--gb), --gb, gb))

    If you do not want to see columns that do not contain data in the applied formulas, then in these formulas you will need to apply the CHOOSECOLS function and select only those columns that contain data.

    Note: Format cells that contain dates as dates and in cases where the cells do not contain dates, format these cells as General.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102.2K Reputation points Volunteer Moderator
    2025-04-22T23:41:36+00:00

    Hi,

    Based on the data that you have shared, show the expected result very clearly.

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2025-04-22T12:15:50+00:00

    For example:

    =SUM(B2:B16*(YEAR(N2:N16)=2023)*(YEAR(S2:S16)=2024))

    and

    =SUM(B2:B16*(YEAR(N2:N16)=2023)*(S2:S16=""))

    Was this answer helpful?

    0 comments