Array Formula

[mattster1010]

Afternoon,

I'm using the below formula to count the number of enquires based on the criteria set in the array formula, however it doesn't seem to bring back the correct amount of enquires when i check manually, can anyone point out if there is something wrong with the formula:


=SUM(IF(Status!$J$2:$J$65536="QCKCT",1,0)*IF(Status!$M$2:$M$65536<>"MULSC",1,0)*IF(Status!$F$2:$F$65536="DX",1,0)*IF(Status!$C$2:$C$65536<>"DWS Standard.Bundle",1,0)*IF(Status!$O$2:$O$65536<>"Capita Symonds Limited ZWET",1,0)*IF(Status!$O$2:$O$65536<>"Capita Symonds Ltd incorporating MMB Associates",1,0)*IF(Status!$R$2:$R$65536<>"Severn Trent",1,0)*IF(Status!$R$2:$R$65536<>"Anglian",1,0)*IF(Status!$R$2:$R$65536<>"Southern",1,0)*IF(Status!$R$2:$R$65536<>"Wessex",1,0)*IF(Status!$B$2:$B$65536='Formula Sheet'!J$3,1,0))

[Simon Lloyd]

If you are using any excel prior to 2007 you are limited to 7 nested if's

[mattster1010]

Thanks for the info. I have reduced the number of nested IF statements in the formula and it works.

My next question is can IF statements have 'OR' incorporated into the formula? An example of what I'm trying to do as per below:

*IF(Status!$R$2:$R$65536<>("Severn Trent")+("Anglian")+("Southern")+("Wessex"),1,0)

Any help would be great!

[Simon Lloyd]

Yes, its available in excel help, you can construct it like this (assuming + doesn't mean AND!)
=IF(OR(.......

[Bob Phillips]

Try

=SUMPRODUCT(--(Status!$R$2:$R$65<>"Severn Trent"),--(Status!$R$2:$R$65<>"Anglian"),--(Status!$R$2:$R$65<>"Southern"),--(Status!$R$2:$R$65<>"Wessex"))

[Simon Lloyd]

Try

=SUMPRODUCT(--(Status!$R$2:$R$65<>"Severn Trent"),--(Status!$R$2:$R$65<>"Anglian"),--(Status!$R$2:$R$65<>"Southern"),--(Status!$R$2:$R$65<>"Wessex")):rotlaugh: True to form Bob :) have you worked out how to get SUMPRODUCT to make tea yet?

[Bob Phillips]

I am sipping a cup made by my good friend SP at this very moment.