Re: Help with SUMIF

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <Bob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A9F39B18-91B3-4AA6-A719-588706332E0E@xxxxxxxxxxxxxxxx
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 work*** 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 work***
function.
I cannot use a macro (for a variety of reasons).

Any help would be greatly appreciated.

Thanks,
Bob



.

Quantcast