# Horizontal sum of dynamic column range based on multiple criterias

1. ## Horizontal sum of dynamic column range based on multiple criterias

Hi all,

i am currently struggling a bit with the following problem:

My Dataset:

Type Division Name Jan Feb Mar
a D Joe 10 15 16
b D Alex 11 14 30

I have monthly data for the past 3 years and up to May 2020 with 3 criterias (Type, Division, Name) in the above format. The months start in Jan17 and go up to May20. I would like to calculate the sum of a range of months (Jan till X) for a given year and for specific 3 criterias, in order to compare the intrayear progress throughout the periods. I started using a Sumproduct formula like: +sumproduct((Type="a")*(Division="D")*(Name="Alex");Offset(....))

The offset selects the same number of rows for Jan of Year X up until the chosen Month of Year X.

Nevertheless, the sumpoduct does not work and gives me a Value error.

Any ideas how to solve the above in anyway would be greatly appreciated.

Cheers
nf

2. ## Re: Horizontal sum of dynamic column range based on multiple criterias

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

3. ## Re: Horizontal sum of dynamic column range based on multiple criterias

So you have 4 criteria, with month is the fourth
Like this:
``Please Login or Register  to view this content.``
With date range is every 1st date of month, can be formatted as "mm/yyyy" or "mmm-yy" whichever you want.

4. ## Re: Horizontal sum of dynamic column range based on multiple criterias

Thanks a lot for that.

It works perfectly and is a way easier approach than what I initially had in mind.

Cheers
nf

5. ## Re: Horizontal sum of dynamic column range based on multiple criterias

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

#### Thread Information

##### Users Browsing this Thread

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

#### Posting Permissions

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