SUMIF with multiple criteria, by columns

Q

Question

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)
 
Q

Question

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.
 
P

Peo Sjoblom

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
 
K

Ken Wright

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 :)
------------------------------­------------------------------­----------------
 
Top