Multiple conditions with an array formula

[Mansfiep]

In order to provide a summary of performance against about 3000 rows of data, I wish to sum the number of rows in a sheet where several conditions are met.The summary is provided on a separate sheet within the same workbook. This summary sheet also holds the priorities, the target response times and the target response percentages.
The conditions are:
Entry in column A (Priority) is 1 (or 2, 3 or 4)
Entry in column N (Target response time) is greater than a value held in cell B3 in
summary sheet,
Entry in column Q is less than a value held in cell C3 in the summary sheet.

I am trying without success to put together an array formula of the form:
=sum(if(and(condition1 on A1:A9999,condition2 on N1:N9999, condition3 on Q1:Q9999),1))

Can anyone help?

 

[Aladin Akyurek]

On 2002-05-07 02:32, Mansfiep wrote:
In order to provide a summary of performance against about 3000 rows of data, I wish to sum the number of rows in a sheet where several conditions are met.The summary is provided on a separate sheet within the same workbook. This summary sheet also holds the priorities, the target response times and the target response percentages.
The conditions are:
Entry in column A (Priority) is 1 (or 2, 3 or 4)
Entry in column N (Target response time) is greater than a value held in cell B3 in
summary sheet,
Entry in column Q is less than a value held in cell C3 in the summary sheet.

I am trying without success to put together an array formula of the form:
=sum(if(and(condition1 on A1:A9999,condition2 on N1:N9999, condition3 on Q1:Q9999),1))

Can anyone help?

It looks like you want to do multiconditional counting. You didn't tell where the condition1 is, but try:

=SUMPRODUCT((A1:A9999=condition1)*(B1:B9999>B3)*(Q1:Q9999<C3))

normally entered.

Consider also using Pivot Tables to summarize your data.
This message was edited by Aladin Akyurek on 2002-05-07 07:24

 

[Mansfiep]

On 2002-05-07 03:03, Aladin Akyurek wrote:

On 2002-05-07 02:32, Mansfiep wrote:
In order to provide a summary of performance against about 3000 rows of data, I wish to sum the number of rows in a sheet where several conditions are met.The summary is provided on a separate sheet within the same workbook. This summary sheet also holds the priorities, the target response times and the target response percentages.
The conditions are:
Entry in column A (Priority) is 1 (or 2, 3 or 4)
Entry in column N (Target response time) is greater than a value held in cell B3 in
summary sheet,
Entry in column Q is less than a value held in cell C3 in the summary sheet.

I am trying without success to put together an array formula of the form:
=sum(if(and(condition1 on A1:A9999,condition2 on N1:N9999, condition3 on Q1:Q9999),1))

Can anyone help?

It looks like you want to do multiconditional counting. You didn't tell where the condition1 is, but try:

=SUMPRODUCT((A1:A9999=condition1)*(B1:B9999>B3)*(Q1:Q9999<C3))

normally entered.

Consider also using Pivot Tables to summarize your data.

Many thanks for your suggestions - they work exactly as required. I have not seen the multiconditioning counting before.