Array Formula

[WillR]

Is there any way to have two conditions in an array formula (sorry if this is easy - bit thick today)
I typed in the formula below (which works
with just the one condition) but it doesn't seem to work when I add the AND bit...
can anyone help - I want the sum of array ak2:ak63707 where conditions in both col a & col b are met....

(this is formula that dont work)
=SUM(IF(AND('Jan 2002 Data'!$B$2:$B$63707=$C21,'Jan 2002 Data'!$A$2:$A$63707='GB(1078)'!$B$1),'Jan 2002 Data'!$AK$2:$AK$63707))

cheers,

Will

 

[Ian Mac]

On 2002-04-16 05:52, WillR wrote:
Is there any way to have two conditions in an array formula (sorry if this is easy - bit thick today)
I typed in the formula below (which works
with just the one condition) but it doesn't seem to work when I add the AND bit...
can anyone help - I want the sum of array ak2:ak63707 where conditions in both col a & col b are met....

(this is formula that dont work)
=SUM(IF(AND('Jan 2002 Data'!$B$2:$B$63707=$C21,'Jan 2002 Data'!$A$2:$A$63707='GB(1078)'!$B$1),'Jan 2002 Data'!$AK$2:$AK$63707))

cheers,

Will

Not tested:

=SUMPRODUCT(('Jan 2002 Data'!$B$2:$B$63707=$C21)*('Jan 2002 Data'!$A$2:$A$63707='GB(1078)'!$B$1),'Jan 2002 Data'!$AK$2:$AK$63707)

you don't need to array enter this

 

[WillR]

Cheers....worked a treat.

Will.

 

[Aladin Akyurek]

On 2002-04-16 06:21, WillR wrote:
Cheers....worked a treat.

Will.

Are your range Data'!$B$2:$B$63707 is really filled up with data?

Aladin