> and < for Array Sumif ({})

[Kiwi Matt]

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<="200606",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$9999,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

 

[Dave F]

You can try dynamic named ranges: http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[Bernie Deitrick]

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]

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

 

[Kiwi Matt]

Hi Bernie

That does the trick
Thank you very much indeed

 

[vezerid]

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

 

[Bernie Deitrick]

Matt,

You're welcome - Glad to hear it!

Bernie
MS Excel MVP