+ Reply to Thread
Results 1 to 8 of 8

What is wrong with this formula?

  1. #1
    Registered User
    Join Date
    07-31-2014
    Location
    Australia
    MS-Off Ver
    MS Excel 2007
    Posts
    27

    What is wrong with this formula?

    Hi, can anyone help me?

    I would like to count number of occurrences in column Z if two possible values occur as referenced from "Drop down lists" worksheet, in addition to the other columns as noted in the formula. For column Z, if I use a single criteria for example Z9:Z1000,'Drop down lists'!$J$21 or 'Drop down lists'!$J$22 , it counts fine. But when I use both it does not count either. I've tried using nested AND and OR functions to include both 'Drop down lists'!$J$21,Z9 & 'Drop down lists'!$J$22 criteria and still nothing.

    =COUNTIFS(E9:E1000,$F$1,Z9:Z1000,'Drop down lists'!$J$21,Z9:Z1000,'Drop down lists'!$J$22,Y9:Y1000,">="&DB1,Y9:Y1000,"<="&DB2)

    Please help.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: What is wrong with this formula?

    countifs is an AND function

    so for that to count at all

    so at least 1 cell in E9:E1000 will have to = F1
    AND
    at least 1 cell in Z9:Z1000 will have to = J21
    AND
    at least 1 cell in Z9:Z1000 will have to = J22
    AND
    at least 1 cell in Y9:Y1000 will have to > or = DB1 (and be a number and NOT text)
    AND
    at least 1 cell in Y9:Y1000 will have to < or = DB2 (and be a number and NOT text)

    for that formula to count 1

    Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Last edited by etaf; 08-06-2014 at 03:18 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Valued Forum Contributor
    Join Date
    10-06-2008
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    365

    Re: What is wrong with this formula?

    As far as I can understand:
    For both [Z9:Z1000=Drop down lists'!$J$21] and [Z9:Z1000=Drop down lists'!$J$22] to be true, Drop down lists'!$J$21 and $J$22 must have the same value. Is this the case?

  4. #4
    Forum Contributor
    Join Date
    10-07-2011
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    212

    Re: What is wrong with this formula?

    Please Attache sample data sheet due to multiple conditions on single column this error giving , we can try Arrey formula for this.

    Regards,
    Suhas

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: What is wrong with this formula?

    Try this
    Enter as an array formula.
    Please Login or Register  to view this content.
    How to enter ARRAY formula.
    Paste the formula
    Press F2
    Press Ctrl+Shift+Enter Keys together.

  6. #6
    Registered User
    Join Date
    07-31-2014
    Location
    Australia
    MS-Off Ver
    MS Excel 2007
    Posts
    27

    Re: What is wrong with this formula?

    No, Drop down lists'!$J$21 and $J$22 have different values but I would like to count both.

  7. #7
    Registered User
    Join Date
    07-31-2014
    Location
    Australia
    MS-Off Ver
    MS Excel 2007
    Posts
    27

    Re: What is wrong with this formula?

    I think may have worked it out. I will use two cells for each individually and then just sum them. Thanks for your help though.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: What is wrong with this formula?

    glad you got it sorted, as i mentioned the countifs was doing an AND for all the criteria - so they all had to be met

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. What is wrong in my formula?
    By michele3000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2010, 05:29 AM
  2. what's wrong with the formula?
    By Jack Zhong in forum Excel General
    Replies: 9
    Last Post: 08-14-2006, 04:20 AM
  3. What is wrong with this formula?
    By scott45 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-27-2005, 02:05 PM
  4. What is wrong with this formula?
    By zolo33 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2005, 11:54 PM
  5. What's wrong with this formula?
    By Ken M. in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-05-2005, 10:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1