software

I have seen the answers to similar questions which suggest the use of
SUMPRODUCT. I have also seen mention that this works for exact and equal
ranges, but i need to do this for columns, because i will be continuously
adding to the ranges.

My columns are named ranges (month, fce1A, SpEE). Here is the sumif
statement.

=SUMIF(AND(month = quot;Mar 2006quot;, fce1A = true),true,SpEE)

Added to the above.

I have tried the following:
=SUMIF(fce1A, TRUE, SpEE)
it seems to work as does...
=SUMIF(month, quot;Mar 2006quot;, SpEE)

Any thoughts?
Perhaps I should just write a custom function? I've actually tried that but
it's not working properly yet.

quot;Questionquot; wrote:

gt; I have seen the answers to similar questions which suggest the use of
gt; SUMPRODUCT. I have also seen mention that this works for exact and equal
gt; ranges, but i need to do this for columns, because i will be continuously
gt; adding to the ranges.
gt;
gt; My columns are named ranges (month, fce1A, SpEE). Here is the sumif
gt; statement.
gt;
gt; =SUMIF(AND(month = quot;Mar 2006quot;, fce1A = true),true,SpEE)

You can't use sumif for this, you would need sumproduct. I find it hard to
believe that you would need 65536 rows in your calculation but even if
that's the case you can use
1:65535 for your named ranges and then use

=SUMPRODUCT(--(month=3),--(fce1a=TRUE),SpEE)

or

=SUMPRODUCT(--(month=quot;Mar 2006quot;),--(fce1a=TRUE),SpEE)

if your months are text
--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;Questionquot; gt; wrote in message
...
gt; Added to the above.
gt;
gt; I have tried the following:
gt; =SUMIF(fce1A, TRUE, SpEE)
gt; it seems to work as does...
gt; =SUMIF(month, quot;Mar 2006quot;, SpEE)
gt;
gt; Any thoughts?
gt; Perhaps I should just write a custom function? I've actually tried that
gt; but
gt; it's not working properly yet.
gt;
gt; quot;Questionquot; wrote:
gt;
gt;gt; I have seen the answers to similar questions which suggest the use of
gt;gt; SUMPRODUCT. I have also seen mention that this works for exact and equal
gt;gt; ranges, but i need to do this for columns, because i will be continuously
gt;gt; adding to the ranges.
gt;gt;
gt;gt; My columns are named ranges (month, fce1A, SpEE). Here is the sumif
gt;gt; statement.
gt;gt;
gt;gt; =SUMIF(AND(month = quot;Mar 2006quot;, fce1A = true),true,SpEE)
Use SUMPRODUCT with a dynamic range that will increase as you add data.

Debra Dalgleish has an example here for making a Pivot table source dynamic,
but the principle is the same:-

www.contextures.com/xlPivot01.html

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------quot;Questionquot; gt; wrote in message
...
gt; Added to the above.
gt;
gt; I have tried the following:
gt; =SUMIF(fce1A, TRUE, SpEE)
gt; it seems to work as does...
gt; =SUMIF(month, quot;Mar 2006quot;, SpEE)
gt;
gt; Any thoughts?
gt; Perhaps I should just write a custom function? I've actually tried that
gt; but
gt; it's not working properly yet.
gt;
gt; quot;Questionquot; wrote:
gt;
gt;gt; I have seen the answers to similar questions which suggest the use of
gt;gt; SUMPRODUCT. I have also seen mention that this works for exact and equal
gt;gt; ranges, but i need to do this for columns, because i will be continuously
gt;gt; adding to the ranges.
gt;gt;
gt;gt; My columns are named ranges (month, fce1A, SpEE). Here is the sumif
gt;gt; statement.
gt;gt;
gt;gt; =SUMIF(AND(month = quot;Mar 2006quot;, fce1A = true),true,SpEE)