Crafting SUMIF formula

[magmike]

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

 

[GS]

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.

 

[isabelle]

hi magmike,

why not use SUMPRODUCT,


=SUMPRODUCT((M1:M65535)*(V1:V65535>=DATE(2011,11,22))*(V1:V65535<=DATE(2011,12,21)))


--
isabelle



Le 2011-12-22 16:11, magmike a écrit :

 

[isabelle]

note that you can change in the formula
Date (2011,11,22) and Date (2011,11,22)
by the cells address contenent the dates you want.


--
isabelle



Le 2011-12-22 20:59, isabelle a écrit :

 

[isabelle]

oups, contenent = that contain

by the cells address that contain the dates you want.

 

[magmike]

hi magmike,

why not use SUMPRODUCT,

=SUMPRODUCT((M1:M65535)*(V1:V65535>=DATE(2011,11,22))*(V1:V65535<=DATE(2011­,12,21)))

--
isabelle

Le 2011-12-22 16:11, magmike a écrit :







- Show quoted text -

Thanks Isabelle, this worked (after changing the first asterisk to a
comma)!

magmike