SUMIF with multiple criteria, by columns
 

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 = "Mar 2006", fce1A = true),true,SpEE)

SUMIF with multiple criteria, by columns
 

Added to the above.

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

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

"Question" wrote:

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 = "Mar 2006", fce1A = true),true,SpEE)

SUMIF with multiple criteria, by columns
 

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="Mar 2006"),--(fce1a=TRUE),SpEE)

if your months are text



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Question" wrote in message
...
Added to the above.

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

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

"Question" wrote:

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 = "Mar 2006", fce1A = true),true,SpEE)

SUMIF with multiple criteria, by columns
 

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:-

http://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 :-)
------------------------------*------------------------------*----------------


"Question" wrote in message
...
Added to the above.

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

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

"Question" wrote:

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 = "Mar 2006", fce1A = true),true,SpEE)