+ Reply to Thread
Results 1 to 14 of 14

COUNTIF or SUMIF??

  1. 09-04-2014, 05:05 AM #1
    WBlack1234
    WBlack1234 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    COUNTIF or SUMIF??

    Hi there

    I'm trying to create a formula to count (amongst a number of other things) whether the completion date passed the due date for specific cases. I've copied the formula below- I know that everything else works except for that underlined:

    =COUNTIFS('Request Log'!$J:$J,Dashboard!AS$10,'Request Log'!$A:$A,">=1/8/2014",'Request Log'!$A:$A,"<=31/8/2014",'Request Log'!$C:$C,">"&'Request Log'!$B:$B)

    Request log column C is the completion date, Column B is the due date.

    I'm using 2010.

    Many thanks

    Wes
    Last edited by WBlack1234; 09-04-2014 at 07:20 AM.
    Reply With QuoteRegister To Reply
  2. 09-04-2014, 05:12 AM #2
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIF or SUMIF??

    Hi Wes,

    Could you post some sample data in a workbook.
    Anonymise it if necessary.

    BSB.
    Reply With QuoteRegister To Reply
  3. 09-04-2014, 05:30 AM #3
    WBlack1234
    WBlack1234 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    Re: COUNTIF or SUMIF??

    Hi there

    Doc attached for your reference. The chart in which I'm trying to get the formula to work is in the Overdue chart (third to the right on the first worksheet, the data appears on the next.WBlack1234.xls

    Many thanks
    Reply With QuoteRegister To Reply
  4. 09-04-2014, 05:47 AM #4
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: COUNTIF or SUMIF??

    Try this..
    But you have to use two cells as start and end date..to replace bold part
    =sum(index(COUNTIFS(Dashboard!AS$10,'Request Log'!$J:$J,"<="&StartDate_Cell,'Request Log'!$A:$A,">="&EndDate_Cell,'Request Log'!$A:$A,'Request Log'!$C:$C,">"&'Request Log'!$B:$B),))
    Totally untested as you haven't provided any workbook..
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com
    Excel is not a matter of Experience, its a matter of Application.
    Say Thanks, Click * Add Reputation
    Reply With QuoteRegister To Reply
  5. 09-04-2014, 05:48 AM #5
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: COUNTIF or SUMIF??

    if it is for month analysing, you could use a helpcolumn in e.g. cell B on sheet request log to determine the month.

    b7=month(a7).
    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.
    Reply With QuoteRegister To Reply
  6. 09-04-2014, 05:51 AM #6
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: COUNTIF or SUMIF??

    Try this.. otherwise..
    =sum(index(1/(COUNTIFS(Dashboard!AS$10,'Request Log'!$J:$J,"<="&StartDate_Cell,'Request Log'!$A:$A,">="&EndDate_Cell,'Request Log'!$A:$A,'Request Log'!$C:$C,">"&'Request Log'!$B:$B)),))
    Reply With QuoteRegister To Reply
  7. 09-04-2014, 06:01 AM #7
    WBlack1234
    WBlack1234 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    Re: COUNTIF or SUMIF??

    Sorry, neither appears to work. There is a sample workbook attached in an earlier message, if this will assist.

    Many thanks
    Reply With QuoteRegister To Reply
  8. 09-04-2014, 06:04 AM #8
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIF or SUMIF??

    A slightly different approach in the attached.
    Using SUMPRODUCT rather than COUNTIFS.

    Note I've changed the months in column AQ to actual dates, yet they are formatted to look the same as you had them before.
    The beauty of using real dates rather than text dates is you only have to write the formula once then can just copy and paste it into all the cells in that table, rather than adjusting the formulas in each row to take account of how many days are in each month.

    Is this something you can use?

    BSB.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 09-04-2014, 06:33 AM #9
    WBlack1234
    WBlack1234 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    Re: COUNTIF or SUMIF??

    Yes BSB, that's perfect! Thank you very much.

    I just had another thought on this, but would it also be possible for that formula to count aswell when the 'date completed' column is blank and the due column contains a date which has passed today's date?

    If not, no worries- I'd assumed that doing that too might be impossible!
    Reply With QuoteRegister To Reply
  10. 09-04-2014, 06:52 AM #10
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIF or SUMIF??

    Like this? (see attached)

    Worth noting that SUMPRODUCT is not particularly swift to calculate when looking at entire columns as I have here so I would recommend you restrict it to enough rows to cover your data rather than entire columns.
    Even better, set up some dynamic named ranges and use those in the formula to the auto adjust to fit your data length.

    BSB.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 09-04-2014, 07:09 AM #11
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIF or SUMIF??

    Just to demonstrate, attachment now with dynamic named ranges.
    The formulas are easier to understand and it calculates far quicker.

    BSB.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 09-04-2014, 07:17 AM #12
    WBlack1234
    WBlack1234 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    Re: COUNTIF or SUMIF??

    Thanks and thanks again, that is seriously impressive. I clearly have much to learn!

    I'll mark this as solved as it appears that this works

    Many thanks indeed
    Reply With QuoteRegister To Reply
  13. 09-04-2014, 07:22 AM #13
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIF or SUMIF??

    Happy to help
    Reply With QuoteRegister To Reply
  14. 09-05-2014, 04:51 AM #14
    WBlack1234
    WBlack1234 is offline
    Registered User
    Join Date
    09-02-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    12

    Re: COUNTIF or SUMIF??

    http://www.excelforum.com/excel-form...ml#post3824012

    Link to further question.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 and sumif
    By ROHAN999 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-23-2013, 08:27 AM
  2. Excel 2007 : Sumif Help or Countif?
    By AberdeenGDK in forum Excel General
    Replies: 4
    Last Post: 02-01-2012, 10:24 AM
  3. sumif countif
    By sabrinigreen in forum Excel General
    Replies: 12
    Last Post: 10-18-2011, 01:59 PM
  4. Countif/Sumif
    By Cain in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-12-2006, 03:59 PM
  5. Countif/Sumif
    By Cain in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-12-2006, 03:35 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1