+ Reply to Thread
Results 1 to 6 of 6

Very urgent - need help with formulating 2 simple counting formulas

  1. #1
    Registered User
    Join Date
    11-24-2017
    Location
    israel
    MS-Off Ver
    Excel Home and student 2019 for Windows 10
    Posts
    151

    Very urgent - need help with formulating 2 simple counting formulas

    Hi,

    I need urgent help in order to meet a dead line -

    Attached is an excel file with two columns of numbers. The first formula I need to formulate is a formula that will automatically count how many unique numbers there are in a column of numbers which has been sorted from smallest to largest (or vice versa). See example in column D. The amount of times each unique number came up I manually typed in adjacent column E. I need a formula (or Excel function) that will automatically count those values for me. This is such a simple and basic function that I am surprised that there is no function for it in the top function banner of Excel.

    The second counting formula I need is a formula which will count how many times a specified value appears in a cell directly below, then 2 cells below, and then 3 cells below a cell with a specified value. See example in attached file. Column B contains cells with different values. For this example, I have highlighted the cells which contain the value 5. All I need is a counting formula which must be added next to each purple cell in the 3 charts which will automatically count the number of times each value specified in each purple cell came up. I must be able to apply the formula to any number I want to target and not only the number 5.

    I have done the count manually so that when the formulas are typed into each cell in the three charts, the value shown matches the value I have typed into each cell next to the purple cells. This will also help you understand what I need counted. Please paste the formulas directly into the charts in the attached excel file, since I am not a computer programmer so I won't understand how to write out the formula if you explain it to me step by step.

    Thank you so much to whomever can formulate these simple counting formulas.

    Jackblack2
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Very urgent - need help with formulating 2 simple counting formulas

    In H4, =COUNTIFS($B$3:$B$102, 5, $B$4:$B$103, G4) and copy down.

    In H14, =COUNTIFS($B$3:$B$102, 5, $B$5:$B$104, G14) and copy down.

    ...
    Last edited by shg; 11-24-2017 at 02:18 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-24-2017
    Location
    israel
    MS-Off Ver
    Excel Home and student 2019 for Windows 10
    Posts
    151

    Re: Very urgent - need help with formulating 2 simple counting formulas

    Awesome! Thank you very much. Is it possible for you to also write out the formula for the first paragraph of my post?

    Thanks.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Very urgent - need help with formulating 2 simple counting formulas

    =count(d3:d102) + sumproduct(-(d3:d101 = d4:d102))

  5. #5
    Registered User
    Join Date
    11-24-2017
    Location
    israel
    MS-Off Ver
    Excel Home and student 2019 for Windows 10
    Posts
    151

    Re: Very urgent - need help with formulating 2 simple counting formulas

    Thank you so much. I fail to understand why Excel does not incorporate this function into the "sort and filter" function in the top banner, since its such an elementary function which I am sure many people need to do. If you are part of Microsoft, please pass this on. I have mentioned it through the feedback on Excel.

    EDIT- Oops. I just pasted the formula into column E and it does not return the correct count. It counts how many unique numbers there are but what I need counted is how many times each unique number came up (see values in column E).

    Sorry and thanks.
    Last edited by jackblack2; 11-25-2017 at 02:10 PM. Reason: misunderstood my request

  6. #6
    Registered User
    Join Date
    11-24-2017
    Location
    israel
    MS-Off Ver
    Excel Home and student 2019 for Windows 10
    Posts
    151

    Re: Very urgent - need help with formulating 2 simple counting formulas

    @shg

    In case you missed my edit in my last post - I pasted the formula you formulated for me in your most recent reply into column E and it does not return the correct count. It counts how many unique numbers there are but what I need counted is how many times each unique number came up (see values in column E).

    Sorry and thanks again.

+ 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. Formulating word formulas for excel
    By jen0919 in forum Excel Formulas & Functions
    Replies: 43
    Last Post: 11-10-2016, 02:59 AM
  2. Replies: 7
    Last Post: 10-20-2014, 09:33 AM
  3. [SOLVED] URGENT - how do you do simple sums with cells set to date format
    By Muzza86 in forum Excel General
    Replies: 8
    Last Post: 06-25-2014, 07:03 AM
  4. Replies: 6
    Last Post: 05-15-2014, 03:39 PM
  5. Requesting a simple counting function, pretty urgent (VBA)
    By ssdt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2008, 08:47 PM
  6. Simple Question but need urgent help pls...
    By hendnov in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-12-2006, 09:15 AM
  7. Urgent simple formula help
    By pinehead in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2006, 06:10 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