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)

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)

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" <Question@discussions.microsoft.com> wrote in message
news:B152F7AC-F63E-4772-BD14-4168FCEF413C@microsoft.com...
> 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)

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" <Question@discussions.microsoft.com> wrote in message
news:B152F7AC-F63E-4772-BD14-4168FCEF413C@microsoft.com...
> 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)