+ Reply to Thread
Results 1 to 10 of 10

formula to return the count of a value

  1. #1
    Registered User
    Join Date
    04-18-2016
    Location
    Singapore
    MS-Off Ver
    Office 2010
    Posts
    37

    formula to return the count of a value

    Hi all, pls help me with the following.. in this excel I attached I need to return the count of some details if it matches with a data in another cell.
    for example,
    if row D=possession then in row K I will need to get the count of 11-07-16. In this case answer is 1. But my formula is indicating 5.
    This is what I wrote, =IF(D2:D20="Possession", COUNTIF(A2:A20,"11-07-16"),0)

    Also what formula should I write if I would like to check for both Possession and Shifted together and return the value of 11-07-16 in row K?
    Attached Files Attached Files
    Last edited by DAVIDZZZ; 07-17-2016 at 09:19 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,953

    Re: formula to return the count of a value

    Try this instead:

    =COUNTIFS(D2:D20,"Possession",A2:A20,"11-07-16")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: formula to return the count of a value

    Try

    K2=COUNTIFS($D$2:$D$20,"Possession",$A$2:$A$20,K$1) and drag
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Registered User
    Join Date
    04-18-2016
    Location
    Singapore
    MS-Off Ver
    Office 2010
    Posts
    37

    Re: formula to return the count of a value

    Hi all, pls help me with the following.. in this excel I attached I need to return the count of some details if it matches with a data in another cell.

    what formula should I write if I would like to check for both Possession and Shifted together and return the value of 11-07-16 in row K?
    for example i need to get the summation of possession and access or possession and shifted.
    Attached Files Attached Files

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,895

    Re: formula to return the count of a value

    @DavidZZZ
    You have been provided with two responses to your original post. You have not responded to either of these people, but have posted the same question again in a continuation of the thread. Please provide feedback to both Ali and Ankur as to whether their solutions provide you with what you are looking for or what happens when you try out their solutions that does not satisfy you. Your response in post 4 is not helpful to the other forum members attempting to assist you.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Registered User
    Join Date
    04-18-2016
    Location
    Singapore
    MS-Off Ver
    Office 2010
    Posts
    37

    Re: formula to return the count of a value

    Thank you for your help but above given formula is not working.

  7. #7
    Registered User
    Join Date
    04-18-2016
    Location
    Singapore
    MS-Off Ver
    Office 2010
    Posts
    37

    Re: formula to return the count of a value

    Hi Ali,
    your formula works but I when I need to count both Possession and Access together it will return "0". What am looking is to figure out this answer as well, "Also what formula should I write if I would like to check for both Possession and Shifted together and return the value of 11-07-16 in row K?"
    Thanks

  8. #8
    Registered User
    Join Date
    04-18-2016
    Location
    Singapore
    MS-Off Ver
    Office 2010
    Posts
    37

    Re: formula to return the count of a value

    Hi Alan,
    My reply wasn't delivered earlier... Slow internet connection, it is fixed now
    thank you for your notification.

  9. #9
    Registered User
    Join Date
    04-18-2016
    Location
    Singapore
    MS-Off Ver
    Office 2010
    Posts
    37

    Re: formula to return the count of a value

    hi All... I just solved my question... just need to use sum and countifs together.
    =SUM(COUNTIFS(G2:G19,{"Access","Possession"}, A2:A19,"11-07-16")) this formula will give what I want

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,895

    Re: formula to return the count of a value

    In K2 and copy to the right =COUNTIFS($A$2:$A$19,K$1,$D$2:$D$19,"Access")+COUNTIFS($A$2:$A$19,K$1,$D$2:$D$19,"Possesion")
    In K3 and copy to the right =COUNTIFS($A$2:$A$19,K$1,$D$2:$D$19,"Shifted")
    in k4 and copy to the right =COUNTIFS($A$2:$A$19,K$1,$D$2:$D$19,"Postponed")

    It would in the future help if you provided the expected results so that people would know what you are looking for and can test before providing you with a solution.

+ 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] Count formula return all answers in 1 cell
    By CHillFL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2015, 10:48 AM
  2. Count, sum and return value formula
    By lar56 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-05-2015, 05:46 PM
  3. [SOLVED] Formula to Lookup a Table and return a Count value.
    By hammer2 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-03-2014, 12:59 AM
  4. [SOLVED] VLookUP or other formula to count and return a value
    By SVTF in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2014, 12:07 PM
  5. Formula to return count, if two critera are met.
    By SeanLightfoote in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-08-2014, 12:51 PM
  6. Formula to count editions of 999 and return a no.
    By n1kk1 in forum Excel General
    Replies: 14
    Last Post: 01-26-2011, 05:50 AM
  7. Replies: 2
    Last Post: 01-07-2011, 03:30 PM

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