+ Reply to Thread
Results 1 to 7 of 7

COUNTIF or SUMPRODUCT?

  1. #1
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    COUNTIF or SUMPRODUCT?

    WBlack1234 (Completion Time Analysis).xls

    Good morning everyone.

    I'm having trouble getting COUNTIFS to work in counting the working days it takes for our directorates to answer enquiries in each quarter of the year. I've put together this so far, but keep getting the error message:

    =COUNTIFS('Request Log'!$J:$J,Dashboard!BH$38,'Request Log'!$A:$A,">=1/7/2014",'Request Log'!$A:$A,"<=30/9/2014",'Request Log'!$Q:$Q,>=0, 'Request Log'!$Q:$Q,<=10)

    I have attached a copy of the spreadsheet for your reference- the table in which the above formula will be inserted is on the first worksheet (scroll all the way to the right to find it. The table is highlighted in yellow and the specific cell is also highlighted in the same colour.

    Thanks for looking at this one

    Wes
    Last edited by WBlack1234; 09-05-2014 at 07:41 AM.

  2. #2
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    Re: COUNTIF or SUMPRODUCT?

    I should also add that the value in the highlighted cell should be 1. There are no other values inserted in the column other than the one which should tally as '1' in the relevant table.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: COUNTIF or SUMPRODUCT?

    What is differant with this question.

    If it is the same, stay in your earlier question.

    Other forummembers can see, which solutions already been given.

    Or (at least) add in your question a link to the earlier threat.

    http://www.excelforum.com/excel-form...-or-sumif.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    Re: COUNTIF or SUMPRODUCT?

    Thanks, I have done so.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: COUNTIF or SUMPRODUCT?

    Some of your quote symbols aren't correct (you can see they look different in the last 2 criteria) - that may happen if you copy all or part of the formula from Word or somewhere else - try this version with correct quotes

    =COUNTIFS('Request Log'!$J:$J,Dashboard!BH$38,'Request Log'!$A:$A,">=1/7/2014",'Request Log'!$A:$A,"<=30/9/2014",'Request Log'!$Q:$Q,">=0", 'Request Log'!$Q:$Q,"<=10")
    Audere est facere

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,503

    Re: COUNTIF or SUMPRODUCT?

    I think your formula should be:

    =COUNTIFS('Request Log'!$J:$J,BH$38,'Request Log'!$A:$A,">=1/7/2014",'Request Log'!$A:$A,"<=30/9/2014",'Request Log'!$R:$R,">=0", 'Request Log'!$R:$R,"<=10")

    "Working days lapsed" is column R rather than Q.

  7. #7
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    Re: COUNTIF or SUMPRODUCT?

    Between DLL and BSB, this is now solved, thank you both.

    Wes

+ 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. [SOLVED] COUNTIF or SUMPRODUCT?
    By shil99 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-25-2014, 03:02 PM
  2. if with sumproduct or countif or Something else???
    By tboans in forum Excel General
    Replies: 2
    Last Post: 04-12-2012, 10:42 AM
  3. SUMPRODUCT or COUNTIF ?
    By natty76 in forum Excel General
    Replies: 10
    Last Post: 05-23-2011, 09:12 AM
  4. [SOLVED] SumProduct or CountIf
    By Kim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] SUMPRODUCT or COUNTIF??
    By KirstieA in forum Excel General
    Replies: 1
    Last Post: 07-27-2005, 07:06 AM

Tags for this Thread

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