Dynamic sum (non contiguous range)

ANTONIO1981 · Jun 7, 2017

Dear all

I have the following spreadsheet

https://1drv.ms/x/s!Av0OR27GDzktgUPrcRv_W6YdhOjy

I would like to create a formulae in T4 based on a dynamic value placed in cell T2.

My target is to calculate Sum all months to get to a YTD value (YTD=year to date) .

Example: May 16 YTD = January+ February+March+April+May

The issue that i have is that i need to ignore the greyed cells ( cells that sum quarters)

I tried with OFFSET But it doesn't ignore particular cells.

thanks in advance

Antonio

wideboydixon · Jun 7, 2017

Book1

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

2

Aug-16

3

US$

Jan-16

Feb-16

Mar-16

Q1

Apr-16

May-16

Jun-16

Q2

Jul-16

Aug-16

Sep-16

Q3

Oct-16

Nov-16

Dec-16

Q4

FULL YEAR

YTD

4

Volume

95,143

99,687

110,521

305,351

114,324

107,522

120,864

342,710

105,919

112,870

171,018

389,807

169,027

116,995

95,552

381,574

1,419,442

866,850

Sheet1

Cell Formulas
Range	Formula
T4	{=SUM(IF($C$3:$Q$3<=$T$2,IF(MOD(COLUMN($C$3:$Q$3)-2,4)<>0,$C$4:$Q$4)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

Rick Rothstein · Jun 7, 2017

What about this...

=SUM(C4:R4)/2

ANTONIO1981 · Jun 7, 2017

thank you so much for the array formulae, it works amazingly, very helpful

FormR · Jun 7, 2017

HI - here is another option:

Code:

=SUMIFS(C4:INDEX(C4:R4,1,MATCH($T$2,$C$3:$R$3,0)),C3:INDEX(C3:R3,1,MATCH($T$2,$C$3:$R$3,0)),"<>Q*")

ANTONIO1981 · Jun 7, 2017

just perfect, how the formulae will be if we were suming row 5 instead of row 4 , and the result was in T5? thank again!

FormR · Jun 7, 2017

ANTONIO1981 said:
just perfect, how the formulae will be if we were suming row 5 instead of row 4 , and the result was in T5? thank again!

Thinking about it a little more, I think this will suffice:

=SUMIFS(C5:R5,$C$3:$R$3,"<="&$T$2)

wideboydixon · Jun 7, 2017

I get hooked on MOD()

WBD

FormR · Jun 7, 2017

wideboydixon said:
I get hooked on MOD()

Same here, but with INDEX()

ANTONIO1981 · Jun 7, 2017

Thanks to all you are great!

Dynamic sum (non contiguous range)

ANTONIO1981

Board Regular

Excel Facts

wideboydixon

Well-known Member

Rick Rothstein

MrExcel MVP

ANTONIO1981

Board Regular

FormR

MrExcel MVP

ANTONIO1981

Board Regular

FormR

MrExcel MVP

wideboydixon

Well-known Member

FormR

MrExcel MVP

ANTONIO1981

Board Regular

Similar threads

Forum statistics

Share this page

Dynamic sum (non contiguous range)

Board Regular

Excel Facts

Well-known Member

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Well-known Member

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock