Thanks:  0
Likes:  0

# Thread: Multiple conditions with an array formula

1. 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?

2. 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
normally entered.

Consider also using Pivot Tables to summarize your data.

[ This Message was edited by: Aladin Akyurek on 2002-05-07 07:24 ]

3. 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
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•