# Count amounts for years within multiple columns

1. ## 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

2. Hello moaaa,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.

3. ## 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.

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