Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Multiple conditions with an array formula

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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. #3
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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