magmike used his keyboard to write :
> I'm a novice at Excel and am trying to craft a SUMIF formula. The
> formula will be in one cell only and I have named that cell
> "InstalledRevenue". I want to add to the value of "InstalledRevenue"
> the value of any cell in Column M where the value of Column V in the
> same row, is between the dates 11/22/11 and 12/21/11 (our fiscal
> month).
>
> What I have so far is =SUMIF(M:M,... and this where I get stuck! Is it
> possible to do this just within the formula (my preferred choice) or
> will VBA be necessary?
>
> Thanks in advance for your help,
>
> magmike
Mike,
You can do this in XL12+ using SUMIFS(), but to do this in earlier
versions you need to use IF() for each criteria along with AND()/SUM().
I get around doing a single quarter by breaking it up into separate
columns for each month and sum by quarter below these.
In your case the fiscal periods bridge months and so you might want to
use a helper column that calcs the fiscal period as an index from 1 to
12, then just use SUMIF(HelperColumn, MonthIndex, RangeToSum).
I use a helper row above the month columns to store the month indexes
for each column and use a row-absolute/col-relative defined name in the
formula so the formula for the 12 month cols is exactly the same. For
example, if I didn't sum each row according to month my sheet would
display this in 12 cols:
=SUMIF(MONTH(TransactionDate), MonthIndex, Amount)
Fortunately, my project calcs transactions by TransactionDate for each
entry (because it also supports multiple currency), and so a simple
SUM() works across the bottom of the fiscal period columns.
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
