|
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. |
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. |
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. |
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. . |
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. . |
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. . |
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 ... 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 |
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 ... 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 . |
| All times are GMT +1. The time now is 04:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com