countif array formula

[alistair01]

Is excel able to calcute the occurances of a number based on three
conditions? the formula i have tried is
SUM(IF('Audit Trail Log'!$H$2:$H$65=4,IF('Audit Trail
Log'!$I$4:$I$65=13,if,('Audit Trail Log'!$K$2:$K$65=1))))
where column h,i and k are the conditions that have to be fulfilled.
this formula returns #NAME? message. please help.

 

[Pete McCosh]

Alistair,

try this, array-entered:

{=sum(('Audit Trail Log'!$H$2:$H$65=4)*('Audit Trail
Log'!$I$4:$I$65=13)*('Audit Trail Log'!$K$2:$K$65=1))}

Cheers, Pete

 

[Bob Phillips]

or no-array entered

=SUMPRODUCT(('Audit Trail Log'!$H$2:$H$65=4)*('Audit Trail
Log'!$I$2:$I$65=13)*('Audit Trail Log'!$K$2:$K$65=1))

Make sure your ranges are the same size, your example wasn't, but I am
assuming that that was a typo.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[alistair01]

Cheers pete that worked a treat