Hi

From a previous post I was provided with a formula to provide a year to date
total from a table based on the month I entered in A1.

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$L$2,0)))

Jan Feb Mar Apr May ................................ Dec
1 1 1 1 1 1 .................................. 1
2
3

This has worked fine but I have now been presented with another table which
has a sub total column which I need to take out when the MATCH includes
certain months.

Jan Feb Mar Jan-Mar Apr May June Apr-June July ...........
Total Total
1 1 1 3 1 1 1 3
1 ............

If I use the original formula when I enter Apr the sum includes Jan, Feb,
Mar and Jan-Mar. I need a formula which removes or ignores Jan-Mar for Apr,
May and June and when I get to July will ignore/remove Jan-Mar ie July
entered in A1 should give me 7 not 13 which the original formula does.

I hope I have made this clear enough and appreciate any assistance.

Try the below version

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$N$2,0)))-
SUMIF(OFFSET($A2,,,1,MATCH($A$1,$A$2:$N$2,0)),"*-*",
OFFSET($A3,,,1,MATCH($A$1,$A$2:$N$2,0)))

--
Jacob (MVP - Excel)


"fabio" wrote:

Hi

From a previous post I was provided with a formula to provide a year to date
total from a table based on the month I entered in A1.

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$L$2,0)))

Jan Feb Mar Apr May ................................ Dec
1 1 1 1 1 1 .................................. 1
2
3

This has worked fine but I have now been presented with another table which
has a sub total column which I need to take out when the MATCH includes
certain months.

Jan Feb Mar Jan-Mar Apr May June Apr-June July ...........
Total Total
1 1 1 3 1 1 1 3
1 ............

If I use the original formula when I enter Apr the sum includes Jan, Feb,
Mar and Jan-Mar. I need a formula which removes or ignores Jan-Mar for Apr,
May and June and when I get to July will ignore/remove Jan-Mar ie July
entered in A1 should give me 7 not 13 which the original formula does.

I hope I have made this clear enough and appreciate any assistance.

Try this

=SUMPRODUCT(--(MOD(COLUMN($A3:$P3),4)<0),--(COLUMN($A3:$P3)<=MATCH($A$1,$A$2:$P$2,0)),$A3:$P3 )

--

HTH

Bob

"fabio" wrote in message
...
Hi

From a previous post I was provided with a formula to provide a year to
date
total from a table based on the month I entered in A1.

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$L$2,0)))

Jan Feb Mar Apr May ................................ Dec
1 1 1 1 1 1
.................................. 1
2
3

This has worked fine but I have now been presented with another table
which
has a sub total column which I need to take out when the MATCH includes
certain months.

Jan Feb Mar Jan-Mar Apr May June Apr-June July ...........
Total Total
1 1 1 3 1 1 1 3
1 ............

If I use the original formula when I enter Apr the sum includes Jan, Feb,
Mar and Jan-Mar. I need a formula which removes or ignores Jan-Mar for
Apr,
May and June and when I get to July will ignore/remove Jan-Mar ie July
entered in A1 should give me 7 not 13 which the original formula does.

I hope I have made this clear enough and appreciate any assistance.

Thank you both Jacob and Bob. While I can follow the logic in Jacob's I'm
still struggling to follow how Bob's works - but it does which is what is
really important.

"Bob Phillips" wrote:

Try this

=SUMPRODUCT(--(MOD(COLUMN($A3:$P3),4)<0),--(COLUMN($A3:$P3)<=MATCH($A$1,$A$2:$P$2,0)),$A3:$P3 )

--

HTH

Bob

"fabio" wrote in message
...
Hi

From a previous post I was provided with a formula to provide a year to
date
total from a table based on the month I entered in A1.

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$L$2,0)))

Jan Feb Mar Apr May ................................ Dec
1 1 1 1 1 1
.................................. 1
2
3

This has worked fine but I have now been presented with another table
which
has a sub total column which I need to take out when the MATCH includes
certain months.

Jan Feb Mar Jan-Mar Apr May June Apr-June July ...........
Total Total
1 1 1 3 1 1 1 3
1 ............

If I use the original formula when I enter Apr the sum includes Jan, Feb,
Mar and Jan-Mar. I need a formula which removes or ignores Jan-Mar for
Apr,
May and June and when I get to July will ignore/remove Jan-Mar ie July
entered in A1 should give me 7 not 13 which the original formula does.

I hope I have made this clear enough and appreciate any assistance.



.

Bob's code ignores 4th,8th,12th columns....and sum up the values from columns
upto the matching column..

--
Jacob (MVP - Excel)


"fabio" wrote:

Thank you both Jacob and Bob. While I can follow the logic in Jacob's I'm
still struggling to follow how Bob's works - but it does which is what is
really important.

"Bob Phillips" wrote:

Try this

=SUMPRODUCT(--(MOD(COLUMN($A3:$P3),4)<0),--(COLUMN($A3:$P3)<=MATCH($A$1,$A$2:$P$2,0)),$A3:$P3 )

--

HTH

Bob

"fabio" wrote in message
...
Hi

From a previous post I was provided with a formula to provide a year to
date
total from a table based on the month I entered in A1.

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$L$2,0)))

Jan Feb Mar Apr May ................................ Dec
1 1 1 1 1 1
.................................. 1
2
3

This has worked fine but I have now been presented with another table
which
has a sub total column which I need to take out when the MATCH includes
certain months.

Jan Feb Mar Jan-Mar Apr May June Apr-June July ...........
Total Total
1 1 1 3 1 1 1 3
1 ............

If I use the original formula when I enter Apr the sum includes Jan, Feb,
Mar and Jan-Mar. I need a formula which removes or ignores Jan-Mar for
Apr,
May and June and when I get to July will ignore/remove Jan-Mar ie July
entered in A1 should give me 7 not 13 which the original formula does.

I hope I have made this clear enough and appreciate any assistance.



.

Hi

I can see that the MOD expression identifies columns devisable by 4 but how
does the SUMPRODUCT ignore these from the range? I'm guessing its the use of
'--' which I have not worked with before.

G

"Jacob Skaria" wrote:

Bob's code ignores 4th,8th,12th columns....and sum up the values from columns
upto the matching column..

--
Jacob (MVP - Excel)


"fabio" wrote:

Thank you both Jacob and Bob. While I can follow the logic in Jacob's I'm
still struggling to follow how Bob's works - but it does which is what is
really important.

"Bob Phillips" wrote:

Try this

=SUMPRODUCT(--(MOD(COLUMN($A3:$P3),4)<0),--(COLUMN($A3:$P3)<=MATCH($A$1,$A$2:$P$2,0)),$A3:$P3 )

--

HTH

Bob

"fabio" wrote in message
...
Hi

From a previous post I was provided with a formula to provide a year to
date
total from a table based on the month I entered in A1.

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$L$2,0)))

Jan Feb Mar Apr May ................................ Dec
1 1 1 1 1 1
.................................. 1
2
3

This has worked fine but I have now been presented with another table
which
has a sub total column which I need to take out when the MATCH includes
certain months.

Jan Feb Mar Jan-Mar Apr May June Apr-June July ...........
Total Total
1 1 1 3 1 1 1 3
1 ............

If I use the original formula when I enter Apr the sum includes Jan, Feb,
Mar and Jan-Mar. I need a formula which removes or ignores Jan-Mar for
Apr,
May and June and when I get to July will ignore/remove Jan-Mar ie July
entered in A1 should give me 7 not 13 which the original formula does.

I hope I have made this clear enough and appreciate any assistance.



.

Hi

MOD(COLUMN($A3:$P3),4)<0
will return True for all of the columns that are not Quarterly Values
The double unary minus -- coerces True to 1 and False to 0
Therefore the values for the columns you want will all be multiplied by 1
and will therefore be included, whereas the values for the Quarterly figures
will be multiplied by Zero, and have no impact upon the final result.

--

Regards
Roger Govier

"fabio" wrote in message
...
Hi

I can see that the MOD expression identifies columns devisable by 4 but
how
does the SUMPRODUCT ignore these from the range? I'm guessing its the use
of
'--' which I have not worked with before.

G

"Jacob Skaria" wrote:

Bob's code ignores 4th,8th,12th columns....and sum up the values from
columns
upto the matching column..

--
Jacob (MVP - Excel)


"fabio" wrote:

Thank you both Jacob and Bob. While I can follow the logic in Jacob's
I'm
still struggling to follow how Bob's works - but it does which is what
is
really important.

"Bob Phillips" wrote:

Try this

=SUMPRODUCT(--(MOD(COLUMN($A3:$P3),4)<0),--(COLUMN($A3:$P3)<=MATCH($A$1,$A$2:$P$2,0)),$A3:$P3 )

--

HTH

Bob

"fabio" wrote in message
...
Hi

From a previous post I was provided with a formula to provide a
year to
date
total from a table based on the month I entered in A1.

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$L$2,0)))

Jan Feb Mar Apr May ................................
Dec
1 1 1 1 1 1
.................................. 1
2
3

This has worked fine but I have now been presented with another
table
which
has a sub total column which I need to take out when the MATCH
includes
certain months.

Jan Feb Mar Jan-Mar Apr May June Apr-June July
...........
Total
Total
1 1 1 3 1 1 1 3
1 ............

If I use the original formula when I enter Apr the sum includes
Jan, Feb,
Mar and Jan-Mar. I need a formula which removes or ignores Jan-Mar
for
Apr,
May and June and when I get to July will ignore/remove Jan-Mar ie
July
entered in A1 should give me 7 not 13 which the original formula
does.

I hope I have made this clear enough and appreciate any assistance.



.


__________ Information from ESET Smart Security, version of virus
signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Hi Roger

Thank you. I follow it now.

"Roger Govier" wrote:

Hi

MOD(COLUMN($A3:$P3),4)<0
will return True for all of the columns that are not Quarterly Values
The double unary minus -- coerces True to 1 and False to 0
Therefore the values for the columns you want will all be multiplied by 1
and will therefore be included, whereas the values for the Quarterly figures
will be multiplied by Zero, and have no impact upon the final result.

--

Regards
Roger Govier

"fabio" wrote in message
...
Hi

I can see that the MOD expression identifies columns devisable by 4 but
how
does the SUMPRODUCT ignore these from the range? I'm guessing its the use
of
'--' which I have not worked with before.

G

"Jacob Skaria" wrote:

Bob's code ignores 4th,8th,12th columns....and sum up the values from
columns
upto the matching column..

--
Jacob (MVP - Excel)


"fabio" wrote:

Thank you both Jacob and Bob. While I can follow the logic in Jacob's
I'm
still struggling to follow how Bob's works - but it does which is what
is
really important.

"Bob Phillips" wrote:

Try this

=SUMPRODUCT(--(MOD(COLUMN($A3:$P3),4)<0),--(COLUMN($A3:$P3)<=MATCH($A$1,$A$2:$P$2,0)),$A3:$P3 )

--

HTH

Bob

"fabio" wrote in message
...
Hi

From a previous post I was provided with a formula to provide a
year to
date
total from a table based on the month I entered in A1.

=SUM(OFFSET($A3,,,1,MATCH($A$1,$A$2:$L$2,0)))

Jan Feb Mar Apr May ................................
Dec
1 1 1 1 1 1
.................................. 1
2
3

This has worked fine but I have now been presented with another
table
which
has a sub total column which I need to take out when the MATCH
includes
certain months.

Jan Feb Mar Jan-Mar Apr May June Apr-June July
...........
Total
Total
1 1 1 3 1 1 1 3
1 ............

If I use the original formula when I enter Apr the sum includes
Jan, Feb,
Mar and Jan-Mar. I need a formula which removes or ignores Jan-Mar
for
Apr,
May and June and when I get to July will ignore/remove Jan-Mar ie
July
entered in A1 should give me 7 not 13 which the original formula
does.

I hope I have made this clear enough and appreciate any assistance.



.


__________ Information from ESET Smart Security, version of virus
signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com



.