+ Reply to Thread
Results 1 to 4 of 4

Count Formulas

  1. #1
    Registered User
    Join Date
    04-25-2012
    Location
    England
    MS-Off Ver
    Excel 2003&2010
    Posts
    18

    Count Formulas

    Hi, I have a list of data with a reference in column A, some rows have a date in column D, I am trying to type a formula in that picks up how many lines of data (for a paticular refernce have a date in column d?) Does anybody know what the formula is?

    For example one of the references in column a is "HD" there are currently over 800 lines of data with that reference, some of those will have a date in column D I just need a quick and easy way of calculating how many, without having to filter etc.
    Regards

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Count Formulas

    Try..

    =COUNTIFS($A$1:$A$100,A1,$D$1:$D$100,">"&1/1/1900)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,135

    Re: Count Formulas

    hi Martin9. since you are also using Excel 2003, you may want to try:
    =SUMPRODUCT((A1:A10="HD")*(D1:D10>1))

    that is assuming your data is from row 1:10.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    04-25-2012
    Location
    England
    MS-Off Ver
    Excel 2003&2010
    Posts
    18

    Re: Count Formulas

    Quote Originally Posted by benishiryo View Post
    hi Martin9. since you are also using Excel 2003, you may want to try:
    =SUMPRODUCT((A1:A10="HD")*(D1:D10>1))

    that is assuming your data is from row 1:10.
    This works perfect for what I need thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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