+ Reply to Thread
Results 1 to 3 of 3

Count amounts for years within multiple columns

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    Switzerland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Count amounts for years within multiple columns

    Hi all,

    I'm trying to automate a report that calculates the amounts by years. To be honest, I'm expecting to do it in Power Bi but let's see if I can have some help here, and then translate it in DAX.
    I'm unfortunately not able to upload my data for any reason, but you can find the data below as an example.


    A B C D E F G H
    1 Year0 AmounY0 Year1 AmounY1 Year2 AmounY2 Year3 AmounY3
    2 2019 10 2020 10 2021 10 2022 10
    3 2020 20 2021 20 2022 20 2023 20
    4 2020 10 2021 10 2022 10 2023 10
    5 2019 10 2020 10 2021 10 2022 10
    6 2019 100 2020 100 2021 100 2022 100
    7 2021 20 2022 20 2023 20 2024 20
    8 2019 10 2020 10 2021 10 2022 10
    9 2020 100 2021 100 2022 100 2023 100
    10 2020 50 2021 50 2022 50 2023 50
    11 2019 20 2020 20 2021 20 2022 20
    12 2019 30 2020 30 2021 30 2022 30
    13 2021 10 2022 10 2023 10 2024 10

    As you can see, the column A contains the years and the columns C, E, G are only incrementing the year by +1.
    What I'd like to do is to have a formula that does the sum of the different amounts in the columns B, D, F, H based on the year that appears in the columns A, C, E, G.
    This should be dynamic, without having to enter the year we want. Any idea?

    Note that I reached to do it by entering the year with the formula:
    =SUMPRODUCT(($A$2:$A$13=K2)*($B$2:$B$13)+($C$2:$C$13=K2)*($D$2:$D$13)+($E$2:$E$13=K2)*($F$2:$F$13)+($G$2:$G$13=K2)*($H$2:$H$13))
    where "K2" contains the year.

    At the end, I'd like to have a graph showing the amounts over the years, as shown in the picture below, and that will for sure add the year 2025 if this year is added in the table.

    Totals.jpg

    Many thanks in advance for your helep

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,146
    Hello moaaa,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,669

    Re: Count amounts for years within multiple columns

    Hello moaaa and Welcome to Excel Forum.
    You have posted in a section of the site that is for introductions, not asking questions. Please repost your question in either the "General" or "Formulas & Functions" section. Also to upload a sample spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1