and < for Array Sumif ({})
 

Hello Everyone

I am trying to do an Array Sumif formula for a discrete range.
I can get it working no problem using <= but not for the other side
I.e.
I have columns of data on worksheet A, months (A), account codes (B) and
values (C)
This formula gives me all values for my account code (cell A11) up to and
including June 2006 (200606)
{=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))}

I need to be able to set up a formula that works for a specific range of
months, for example April to June (200604 to 200606)
I was hoping
{=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))}
would work, but it doesn't.

Does anyone know if this kind of formula is possible, and if so would they
mind suggesting how the exact syntax should go?

Thanks for your time so far, hope I hear from you soon

and < for Array Sumif ({})
 

You can try dynamic named ranges: http://www.ozgrid.com/Excel/DynamicRanges.htm
--
Brevity is the soul of wit.


"Kiwi Matt" wrote:

Hello Everyone

I am trying to do an Array Sumif formula for a discrete range.
I can get it working no problem using <= but not for the other side
I.e.
I have columns of data on worksheet A, months (A), account codes (B) and
values (C)
This formula gives me all values for my account code (cell A11) up to and
including June 2006 (200606)
{=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))}

I need to be able to set up a formula that works for a specific range of
months, for example April to June (200604 to 200606)
I was hoping
{=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))}
would work, but it doesn't.

Does anyone know if this kind of formula is possible, and if so would they
mind suggesting how the exact syntax should go?

Thanks for your time so far, hope I hear from you soon

and < for Array Sumif ({})
 

Matt,

Try:

=SUMPRODUCT((A!$B$1:$B$9999=$A11)*(A!$A$1:$A$9999 =200604)*(A!$A$1:$A$9999<=200606)*(A!$C$1:$C$9999) )

and if the date values are strings and not numbers:

=SUMPRODUCT((A!$B$1:$B$9999=$F6)*(VALUE(A!$A$1:$A$ 9999)=200604)*(VALUE(A!$A$1:$A$9999)<=200606)*(A! $C$1:$C$9999))

HTH,
Bernie
MS Excel MVP


"Kiwi Matt" wrote in message
...
Hello Everyone

I am trying to do an Array Sumif formula for a discrete range.
I can get it working no problem using <= but not for the other side
I.e.
I have columns of data on worksheet A, months (A), account codes (B) and
values (C)
This formula gives me all values for my account code (cell A11) up to and
including June 2006 (200606)
{=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))}

I need to be able to set up a formula that works for a specific range of
months, for example April to June (200604 to 200606)
I was hoping
{=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))}
would work, but it doesn't.

Does anyone know if this kind of formula is possible, and if so would they
mind suggesting how the exact syntax should go?

Thanks for your time so far, hope I hear from you soon

and < for Array Sumif ({})
 

Thanks Dave

But I don't think this would work, the data is not sorted by month so
wouldn't be in a continuous range

Matt


"Dave F" wrote:

You can try dynamic named ranges: http://www.ozgrid.com/Excel/DynamicRanges.htm
--
Brevity is the soul of wit.


"Kiwi Matt" wrote:

Hello Everyone

I am trying to do an Array Sumif formula for a discrete range.
I can get it working no problem using <= but not for the other side
I.e.
I have columns of data on worksheet A, months (A), account codes (B) and
values (C)
This formula gives me all values for my account code (cell A11) up to and
including June 2006 (200606)
{=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))}

I need to be able to set up a formula that works for a specific range of
months, for example April to June (200604 to 200606)
I was hoping
{=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))}
would work, but it doesn't.

Does anyone know if this kind of formula is possible, and if so would they
mind suggesting how the exact syntax should go?

Thanks for your time so far, hope I hear from you soon

and < for Array Sumif ({})
 

Hi Bernie

That does the trick
Thank you very much indeed



"Bernie Deitrick" wrote:

Matt,

Try:

=SUMPRODUCT((A!$B$1:$B$9999=$A11)*(A!$A$1:$A$9999 =200604)*(A!$A$1:$A$9999<=200606)*(A!$C$1:$C$9999) )

and if the date values are strings and not numbers:

=SUMPRODUCT((A!$B$1:$B$9999=$F6)*(VALUE(A!$A$1:$A$ 9999)=200604)*(VALUE(A!$A$1:$A$9999)<=200606)*(A! $C$1:$C$9999))

HTH,
Bernie
MS Excel MVP


"Kiwi Matt" wrote in message
...
Hello Everyone

I am trying to do an Array Sumif formula for a discrete range.
I can get it working no problem using <= but not for the other side
I.e.
I have columns of data on worksheet A, months (A), account codes (B) and
values (C)
This formula gives me all values for my account code (cell A11) up to and
including June 2006 (200606)
{=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))}

I need to be able to set up a formula that works for a specific range of
months, for example April to June (200604 to 200606)
I was hoping
{=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))}
would work, but it doesn't.

Does anyone know if this kind of formula is possible, and if so would they
mind suggesting how the exact syntax should go?

Thanks for your time so far, hope I hear from you soon

and < for Array Sumif ({})
 

Matt,

the formula Bernie gave you does not require sorting. All it requires
is that your data do not exceed row 9999

HTH
Kostis Vezerides


Kiwi Matt wrote:
Thanks Dave

But I don't think this would work, the data is not sorted by month so
wouldn't be in a continuous range

Matt


"Dave F" wrote:

You can try dynamic named ranges: http://www.ozgrid.com/Excel/DynamicRanges.htm
--
Brevity is the soul of wit.


"Kiwi Matt" wrote:

Hello Everyone

I am trying to do an Array Sumif formula for a discrete range.
I can get it working no problem using <= but not for the other side
I.e.
I have columns of data on worksheet A, months (A), account codes (B) and
values (C)
This formula gives me all values for my account code (cell A11) up to and
including June 2006 (200606)
{=SUM(IF(A!$B$1:$B$9999=$A11,IF(A!$A$1:$A$9999<="2 00606",A!$C$1:$C$9999,0),0))}

I need to be able to set up a formula that works for a specific range of
months, for example April to June (200604 to 200606)
I was hoping
{=SUM(IF(A!$B$1:$B$9999=$A12,IF(AND(A!$A$1:$A$9999 ="2006004","200606"<=A!$A$1:$A$9999),A!$C$1:$C$99 99,0),0))}
would work, but it doesn't.

Does anyone know if this kind of formula is possible, and if so would they
mind suggesting how the exact syntax should go?

Thanks for your time so far, hope I hear from you soon

and < for Array Sumif ({})
 

Matt,

You're welcome - Glad to hear it!

Bernie
MS Excel MVP

That does the trick
Thank you very much indeed