Sum named range consists of several columns and rows

Question

I have a list that is divided into countries vertical and years horizontal like below.

I need to sum all numbers for 2020 respective for each country. Each country have several lines divided into different months.

             2020                     2021
         J | F | M | A | M |...| J | F | M | A | M |...
-------------------------------------------------------
Denmark |  |   | 15|   | 12|   |   |   |   |   |   |
Norway  |  |   |   |   |   |   |   |   | 10|   |   |
Germany |  |   |   | 11|   |   |   |   |   |   |   |

Each year have been called a named range, e.g. Year2020. I have tried using =SUMPRODUCT(SUMIFS(Year2020;CountryRNG;Country)), MATCH/INDEX and also =SUM(INDEX(Year2020;0;MATCH(1E+99;INDEX(Year2020;1;0)))).

How can I do this with one formula?

Scott Craner · Accepted Answer · 2019-11-26 14:26:46Z

1

You can use SUMPRODUCT:

=SUMPRODUCT((Country=CountryRNG)*Year2020)

With a few notes:

CountryRNG and Year2020 have the same number of rows
Year2020 is only the data. No Text or Errors in the data field
Both ranges are limited to the data and does not include full column references. This is to limit the number of iterations that will slow down the calcs. It will work with extra rows, but the more unneeded iteration will cause extra work.

answered Nov 26, 2019 at 14:26

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon J Over a year ago

Thank you. This works like a charm. I added Indirect("Year"&CellWithYear) also to make it dynamic.

Collectives™ on Stack Overflow

Sum named range consists of several columns and rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related