Help with SUMIF
 

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt., respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt., respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt., respectively.
etc.

Based on a pull-down box I created where a user selects an ending period
(month-year), I'm trying to use the SUMIF function whereby it should add up
only the "Mgmt." columns, and only up through the selcted ending period. So
even though the worksheet contains data for all the months through the end of
2010, if a user selects Apr 2009 as the ending period, for example, I need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and including) Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I could
somehow use SUMIF to look for and stop adding every other column when it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

For whatever it's worth, below is the current formula I've written, but it
only works when the data is in consecutive columns, not when the data is in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt., respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt., respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt., respectively.
etc.

Based on a pull-down box I created where a user selects an ending period
(month-year), I'm trying to use the SUMIF function whereby it should add up
only the "Mgmt." columns, and only up through the selcted ending period. So
even though the worksheet contains data for all the months through the end of
2010, if a user selects Apr 2009 as the ending period, for example, I need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and including) Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I could
somehow use SUMIF to look for and stop adding every other column when it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Assuing the dates are in row 1, amounts in 2, and testing for up to and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've written, but it
only works when the data is in consecutive columns, not when the data is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt., respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt., respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt., respectively.
etc.

Based on a pull-down box I created where a user selects an ending period
(month-year), I'm trying to use the SUMIF function whereby it should add
up
only the "Mgmt." columns, and only up through the selcted ending period.
So
even though the worksheet contains data for all the months through the
end of
2010, if a user selects Apr 2009 as the ending period, for example, I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I could
somehow use SUMIF to look for and stop adding every other column when it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Bob,

Thanks for the suggestion. However, I have several rows of data, and each
row needs to be summed separately. How would I modify your formula to do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've written, but it
only works when the data is in consecutive columns, not when the data is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt., respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt., respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt., respectively.
etc.

Based on a pull-down box I created where a user selects an ending period
(month-year), I'm trying to use the SUMIF function whereby it should add
up
only the "Mgmt." columns, and only up through the selcted ending period.
So
even though the worksheet contains data for all the months through the
end of
2010, if a user selects Apr 2009 as the ending period, for example, I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I could
somehow use SUMIF to look for and stop adding every other column when it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Bob -

Your spreadsheet design is what's causing you this heartache. Analysis of
data like this is far, FAR easier if you have it arranged in database format
- meaning the first column indicates the date, second indicates Mgt or
NonMgt, third column indicates account number or category or whatever
currently differentiates your rows, then the final column is the value.

From that structure you can whip out a pivot table lightning fast, or use
straightforward SUMPRODUCT()s to summarize the data.

You stated that you can't use a macro to solve this, meaning that, unless
you restructure your data, you are going to have to use convoluted formulae

Bob Phillips' formula can be modified to sum row 3 by changing the 2:2
instances to 3:3 instances, and you may want to change the 1:1 reference to
$1:$1 so you can copy the formula to other rows

=SUMPRODUCT(--(MOD(COLUMN(3:3),2)*1),--($1:$1<=--"2009/04/30"),3:3)

"Bob" wrote:

Bob,

Thanks for the suggestion. However, I have several rows of data, and each
row needs to be summed separately. How would I modify your formula to do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've written, but it
only works when the data is in consecutive columns, not when the data is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt., respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt., respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt., respectively.
etc.

Based on a pull-down box I created where a user selects an ending period
(month-year), I'm trying to use the SUMIF function whereby it should add
up
only the "Mgmt." columns, and only up through the selcted ending period.
So
even though the worksheet contains data for all the months through the
end of
2010, if a user selects Apr 2009 as the ending period, for example, I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I could
somehow use SUMIF to look for and stop adding every other column when it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Assuming your results are in N2 etc

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)*1),--(1:1<=--"2009/04/30"),A2:M2)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Bob" wrote in message
...
Bob,

Thanks for the suggestion. However, I have several rows of data, and each
row needs to be summed separately. How would I modify your formula to do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've written, but
it
only works when the data is in consecutive columns, not when the data
is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt., respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt., respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt., respectively.
etc.

Based on a pull-down box I created where a user selects an ending
period
(month-year), I'm trying to use the SUMIF function whereby it should
add
up
only the "Mgmt." columns, and only up through the selcted ending
period.
So
even though the worksheet contains data for all the months through the
end of
2010, if a user selects Apr 2009 as the ending period, for example, I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and
including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I
could
somehow use SUMIF to look for and stop adding every other column when
it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Duke,

Thank you for your thoughtful comments and suggestions. I sincerely
appreciate it.

As it turns out, the worksheet I'm trying to build is based on an existing
paper form that end users are currently filling out. If I rearrange the
columns to make the worksheet more database-like, or utilize a pivot table, I
think the end users will avoid using Excel altogether. (It's bad enough just
getting them to move from the paper form to an "electronic" form.) :-)

Be that as it may, I was really hoping that SUMPRODUCT would do the trick.
To be candid, although I'm very familiar and comfortable using most of
Excel's built-in functions, I have never fully understood how SUMPRODUCT
works when there is more than two arrays involved or when "--" is used. And
given that there is over 200 rows of data in my worksheet, having to manually
modify the "constants" within the SUMPRODUCT formula for each row does not
make sense.

Although my worksheet contains 24 months (i.e., 48 columns) of data, I also
need a way to prevent SUM, or SUMIF, or SUMPRODUCT from adding all the
columns when only a subset of columns has be selected (per the drop-down box
I mentioned in my original post), and then only SUM, or SUMIF, or SUMPRODUCT
every other column.

Oh well, it looks like it's back to square one.

Thanks again,
Bob


"Duke Carey" wrote:

Bob -

Your spreadsheet design is what's causing you this heartache. Analysis of
data like this is far, FAR easier if you have it arranged in database format
- meaning the first column indicates the date, second indicates Mgt or
NonMgt, third column indicates account number or category or whatever
currently differentiates your rows, then the final column is the value.

From that structure you can whip out a pivot table lightning fast, or use
straightforward SUMPRODUCT()s to summarize the data.

You stated that you can't use a macro to solve this, meaning that, unless
you restructure your data, you are going to have to use convoluted formulae

Bob Phillips' formula can be modified to sum row 3 by changing the 2:2
instances to 3:3 instances, and you may want to change the 1:1 reference to
$1:$1 so you can copy the formula to other rows

=SUMPRODUCT(--(MOD(COLUMN(3:3),2)*1),--($1:$1<=--"2009/04/30"),3:3)

"Bob" wrote:

Bob,

Thanks for the suggestion. However, I have several rows of data, and each
row needs to be summed separately. How would I modify your formula to do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've written, but it
only works when the data is in consecutive columns, not when the data is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt., respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt., respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt., respectively.
etc.

Based on a pull-down box I created where a user selects an ending period
(month-year), I'm trying to use the SUMIF function whereby it should add
up
only the "Mgmt." columns, and only up through the selcted ending period.
So
even though the worksheet contains data for all the months through the
end of
2010, if a user selects Apr 2009 as the ending period, for example, I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I could
somehow use SUMIF to look for and stop adding every other column when it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Bob,

That did the trick! I even was able to replace "2009/04/30" with a VLOOKUP
formula.

Since your formula adds up all the Mgmt. columns (e.g., A, C, E, etc.),
could you kindly tell me how to modify it to add up all the Non-Mgmt. columns
(e.g., B, D, F, etc.)?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuming your results are in N2 etc

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)*1),--(1:1<=--"2009/04/30"),A2:M2)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Bob" wrote in message
...
Bob,

Thanks for the suggestion. However, I have several rows of data, and each
row needs to be summed separately. How would I modify your formula to do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've written, but
it
only works when the data is in consecutive columns, not when the data
is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt., respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt., respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt., respectively.
etc.

Based on a pull-down box I created where a user selects an ending
period
(month-year), I'm trying to use the SUMIF function whereby it should
add
up
only the "Mgmt." columns, and only up through the selcted ending
period.
So
even though the worksheet contains data for all the months through the
end of
2010, if a user selects Apr 2009 as the ending period, for example, I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and
including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I
could
somehow use SUMIF to look for and stop adding every other column when
it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Actually, there were typos in that formula it should have been

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=1),--(A1:M1<=--"2009/04/30"),A2:M2)

and then


=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=0),--(A1:M1<=--"2009/04/30"),A2:M2)



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
Bob,

That did the trick! I even was able to replace "2009/04/30" with a
VLOOKUP
formula.

Since your formula adds up all the Mgmt. columns (e.g., A, C, E, etc.),
could you kindly tell me how to modify it to add up all the Non-Mgmt.
columns
(e.g., B, D, F, etc.)?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuming your results are in N2 etc

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)*1),--(1:1<=--"2009/04/30"),A2:M2)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Bob" wrote in message
...
Bob,

Thanks for the suggestion. However, I have several rows of data, and
each
row needs to be summed separately. How would I modify your formula to
do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to
and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've written,
but
it
only works when the data is in consecutive columns, not when the
data
is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt.,
respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt.,
respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt.,
respectively.
etc.

Based on a pull-down box I created where a user selects an ending
period
(month-year), I'm trying to use the SUMIF function whereby it
should
add
up
only the "Mgmt." columns, and only up through the selcted ending
period.
So
even though the worksheet contains data for all the months through
the
end of
2010, if a user selects Apr 2009 as the ending period, for example,
I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and
including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I
could
somehow use SUMIF to look for and stop adding every other column
when
it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Bob,

It's interesting that *1 also worked in the first formula, but maybe it was
just a coincidence.

Anyway, thanks a million for all your help! I sincerely appreciate it.

Bob


"Bob Phillips" wrote:

Actually, there were typos in that formula it should have been

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=1),--(A1:M1<=--"2009/04/30"),A2:M2)

and then


=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=0),--(A1:M1<=--"2009/04/30"),A2:M2)



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
Bob,

That did the trick! I even was able to replace "2009/04/30" with a
VLOOKUP
formula.

Since your formula adds up all the Mgmt. columns (e.g., A, C, E, etc.),
could you kindly tell me how to modify it to add up all the Non-Mgmt.
columns
(e.g., B, D, F, etc.)?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuming your results are in N2 etc

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)*1),--(1:1<=--"2009/04/30"),A2:M2)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Bob" wrote in message
...
Bob,

Thanks for the suggestion. However, I have several rows of data, and
each
row needs to be summed separately. How would I modify your formula to
do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to
and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've written,
but
it
only works when the data is in consecutive columns, not when the
data
is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt.,
respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt.,
respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt.,
respectively.
etc.

Based on a pull-down box I created where a user selects an ending
period
(month-year), I'm trying to use the SUMIF function whereby it
should
add
up
only the "Mgmt." columns, and only up through the selcted ending
period.
So
even though the worksheet contains data for all the months through
the
end of
2010, if a user selects Apr 2009 as the ending period, for example,
I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and
including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the selected
month-year are automatically shown as being blank, thinking that I
could
somehow use SUMIF to look for and stop adding every other column
when
it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

It worked because any non-zero value is TRUE, so for the odd columns *1
comes out as true as well as =1. In fact, for odd row, you don't need *1 or
=1, it works with neither, but it just is more obvious with =1 IMO.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
Bob,

It's interesting that *1 also worked in the first formula, but maybe it
was
just a coincidence.

Anyway, thanks a million for all your help! I sincerely appreciate it.

Bob


"Bob Phillips" wrote:

Actually, there were typos in that formula it should have been

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=1),--(A1:M1<=--"2009/04/30"),A2:M2)

and then


=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=0),--(A1:M1<=--"2009/04/30"),A2:M2)



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob" wrote in message
...
Bob,

That did the trick! I even was able to replace "2009/04/30" with a
VLOOKUP
formula.

Since your formula adds up all the Mgmt. columns (e.g., A, C, E, etc.),
could you kindly tell me how to modify it to add up all the Non-Mgmt.
columns
(e.g., B, D, F, etc.)?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuming your results are in N2 etc

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)*1),--(1:1<=--"2009/04/30"),A2:M2)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Bob" wrote in message
...
Bob,

Thanks for the suggestion. However, I have several rows of data,
and
each
row needs to be summed separately. How would I modify your formula
to
do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to
and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've
written,
but
it
only works when the data is in consecutive columns, not when the
data
is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt.,
respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt.,
respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt.,
respectively.
etc.

Based on a pull-down box I created where a user selects an
ending
period
(month-year), I'm trying to use the SUMIF function whereby it
should
add
up
only the "Mgmt." columns, and only up through the selcted ending
period.
So
even though the worksheet contains data for all the months
through
the
end of
2010, if a user selects Apr 2009 as the ending period, for
example,
I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and
including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the
selected
month-year are automatically shown as being blank, thinking that
I
could
somehow use SUMIF to look for and stop adding every other column
when
it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a
worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Bob,

I finally had an opportunity this morning to input your 2nd formula in my
worksheet for summing the Non-Mgmt. columns:

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=0),--(A1:M1<=--"2009/04/30"),A2:M2)

Unfortunately, it is summing ALL the Non-Mgmt. columns, including those
whose dates are beyond 2009/04/30. Do you know why it is behaving this way?

Thanks again,
Bob


"Bob Phillips" wrote:

It worked because any non-zero value is TRUE, so for the odd columns *1
comes out as true as well as =1. In fact, for odd row, you don't need *1 or
=1, it works with neither, but it just is more obvious with =1 IMO.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
Bob,

It's interesting that *1 also worked in the first formula, but maybe it
was
just a coincidence.

Anyway, thanks a million for all your help! I sincerely appreciate it.

Bob


"Bob Phillips" wrote:

Actually, there were typos in that formula it should have been

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=1),--(A1:M1<=--"2009/04/30"),A2:M2)

and then


=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=0),--(A1:M1<=--"2009/04/30"),A2:M2)



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob" wrote in message
...
Bob,

That did the trick! I even was able to replace "2009/04/30" with a
VLOOKUP
formula.

Since your formula adds up all the Mgmt. columns (e.g., A, C, E, etc.),
could you kindly tell me how to modify it to add up all the Non-Mgmt.
columns
(e.g., B, D, F, etc.)?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuming your results are in N2 etc

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)*1),--(1:1<=--"2009/04/30"),A2:M2)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Bob" wrote in message
...
Bob,

Thanks for the suggestion. However, I have several rows of data,
and
each
row needs to be summed separately. How would I modify your formula
to
do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to
and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've
written,
but
it
only works when the data is in consecutive columns, not when the
data
is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt.,
respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt.,
respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt.,
respectively.
etc.

Based on a pull-down box I created where a user selects an
ending
period
(month-year), I'm trying to use the SUMIF function whereby it
should
add
up
only the "Mgmt." columns, and only up through the selcted ending
period.
So
even though the worksheet contains data for all the months
through
the
end of
2010, if a user selects Apr 2009 as the ending period, for
example,
I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and
including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the
selected
month-year are automatically shown as being blank, thinking that
I
could
somehow use SUMIF to look for and stop adding every other column
when
it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a
worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob

Help with SUMIF
 

Bob,

Never mind. I figured out the problem. Sorry for any inconvenience.

Bob


"Bob Phillips" wrote:

It worked because any non-zero value is TRUE, so for the odd columns *1
comes out as true as well as =1. In fact, for odd row, you don't need *1 or
=1, it works with neither, but it just is more obvious with =1 IMO.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob" wrote in message
...
Bob,

It's interesting that *1 also worked in the first formula, but maybe it
was
just a coincidence.

Anyway, thanks a million for all your help! I sincerely appreciate it.

Bob


"Bob Phillips" wrote:

Actually, there were typos in that formula it should have been

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=1),--(A1:M1<=--"2009/04/30"),A2:M2)

and then


=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)=0),--(A1:M1<=--"2009/04/30"),A2:M2)



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob" wrote in message
...
Bob,

That did the trick! I even was able to replace "2009/04/30" with a
VLOOKUP
formula.

Since your formula adds up all the Mgmt. columns (e.g., A, C, E, etc.),
could you kindly tell me how to modify it to add up all the Non-Mgmt.
columns
(e.g., B, D, F, etc.)?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuming your results are in N2 etc

=SUMPRODUCT(--(MOD(COLUMN(A2:M2),2)*1),--(1:1<=--"2009/04/30"),A2:M2)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Bob" wrote in message
...
Bob,

Thanks for the suggestion. However, I have several rows of data,
and
each
row needs to be summed separately. How would I modify your formula
to
do
that?

Thanks again,
Bob


"Bob Phillips" wrote:

Assuing the dates are in row 1, amounts in 2, and testing for up to
and
inclduing 30th April

=SUMPRODUCT(--(MOD(COLUMN(2:2),2)*1),--(1:1<=--"2009/04/30"),2:2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Bob" wrote in message
...
For whatever it's worth, below is the current formula I've
written,
but
it
only works when the data is in consecutive columns, not when the
data
is
in
every other column:

=SUMIF(INDIRECT("A2:"&ADDRESS(ROW(),MATCH(MAX($A1: $AJ1)+1,$A1:$AJ1,1))),"0")



"Bob" wrote:

Columns A & B = Jan 2008 - Mgmt. and Jan 2008 Non-Mgmt.,
respectively.
Columns C & D = Feb 2008 - Mgmt. and Feb 2008 Non-Mgmt.,
respectively.
Columns E & F = Mar 2008 - Mgmt. and Mar 2008 Non-Mgmt.,
respectively.
etc.

Based on a pull-down box I created where a user selects an
ending
period
(month-year), I'm trying to use the SUMIF function whereby it
should
add
up
only the "Mgmt." columns, and only up through the selcted ending
period.
So
even though the worksheet contains data for all the months
through
the
end of
2010, if a user selects Apr 2009 as the ending period, for
example,
I
need
SUMIF to add only the "Mgmt." columns from Jan 2008 up to (and
including)
Apr
2009.

FYI - I rigged it so that the month-year labels beyond the
selected
month-year are automatically shown as being blank, thinking that
I
could
somehow use SUMIF to look for and stop adding every other column
when
it
encountered the first blank month-year label. But no such luck.

BTW, I need to solve the aforementioned problem using a
worksheet
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob