A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Summing a non continuous range



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 09:12 AM posted to microsoft.public.excel.worksheet.functions
Fabio
external usenet poster
 
Posts: 45
Default Summing a non continuous range

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.

  #2  
Old June 1st, 2010, 10:45 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Summing a non continuous range

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.

  #3  
Old June 1st, 2010, 10:45 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Summing a non continuous range

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.



  #4  
Old June 1st, 2010, 12:24 PM posted to microsoft.public.excel.worksheet.functions
Fabio
external usenet poster
 
Posts: 45
Default Summing a non continuous range

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.



.

  #5  
Old June 1st, 2010, 12:37 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Summing a non continuous range

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.



.

  #6  
Old June 2nd, 2010, 01:34 PM posted to microsoft.public.excel.worksheet.functions
Fabio
external usenet poster
 
Posts: 45
Default Summing a non continuous range

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.



.

  #7  
Old June 2nd, 2010, 02:06 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Summing a non continuous range

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
news
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



  #8  
Old June 2nd, 2010, 04:23 PM posted to microsoft.public.excel.worksheet.functions
Fabio
external usenet poster
 
Posts: 45
Default Summing a non continuous range

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
news
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



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.