+ Reply to Thread
Results 1 to 16 of 16

Counting columns if their sum is equal to no.

  1. #1
    Registered User
    Join Date
    10-20-2017
    Location
    Egypt
    MS-Off Ver
    Microsoft office 2010
    Posts
    15

    Counting columns if their sum is equal to no.

    Col. A I want to make a formula that counts Col. A and get me the no. of cells it counts when sum of the counted cells match certain #
    250
    250
    250
    250
    250
    250
    250
    250
    250
    250
    250
    250
    200
    200

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Counting columns if their sum is equal to no.

    I'm not sure that I understand exactly what it is that you are looking for.

    That being said, if you want to take the sum of column A, that would be this: =SUM(A:A)

    If you want to count the amount of numbers in column A, that would be htis: =COUNT(A:A)

    If that doesn't answer your question, consider creating a small representative sample of your data along with the desired result of the formula. You can upload a sample Excel workbook by clicking on the GO ADVANCED and then scrolling down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    10-20-2017
    Location
    Egypt
    MS-Off Ver
    Microsoft office 2010
    Posts
    15

    Re: Counting columns if their sum is equal to no.

    am afraid i couldn't explain my problem correctly :D

    Actually i want the function to count the cells until it get the sum to be 1500 for example. so the count function will have a result of 6

    i hope i could deliver it this time :D

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Counting columns if their sum is equal to no.

    Hi all. Try this:
    Please Login or Register  to view this content.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  5. #5
    Registered User
    Join Date
    10-20-2017
    Location
    Egypt
    MS-Off Ver
    Microsoft office 2010
    Posts
    15

    Re: Counting columns if their sum is equal to no.

    It didn't work,

    attached a file with the required, thanks in advance for your help. i hope you can help me
    Attached Files Attached Files

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Counting columns if their sum is equal to no.

    And here's a version that works for greater-than-or equal-to :
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Counting columns if their sum is equal to no.

    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  8. #8
    Registered User
    Join Date
    10-20-2017
    Location
    Egypt
    MS-Off Ver
    Microsoft office 2010
    Posts
    15

    Re: Counting columns if their sum is equal to no.

    Thanks alot,

    but can you rewrite the formula for the attached sheet as i can't update it to match

  9. #9
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Counting columns if their sum is equal to no.

    Is this what you're after (paste in C3 and copy down)?
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-20-2017
    Location
    Egypt
    MS-Off Ver
    Microsoft office 2010
    Posts
    15

    Re: Counting columns if their sum is equal to no.

    It counted wrong again,

    it counts 34 counts to get 6600 while it should count 29


    i have attached again the same file with a yellow comment so you can understand my concern,

    i really appreciate your help

    Thanks in advance
    Attached Files Attached Files

  11. #11
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Counting columns if their sum is equal to no.

    That's the number of days INCLUDING BLANKS (meaning the number of rows from point counted). Any other formulas using this as a lookup will probably need the row count. Perhaps you could clarify and expand on how you intend to use the info.
    Last edited by leelnich; 12-26-2017 at 04:19 PM.

  12. #12
    Registered User
    Join Date
    10-20-2017
    Location
    Egypt
    MS-Off Ver
    Microsoft office 2010
    Posts
    15

    Re: Counting columns if their sum is equal to no.

    i am want to use the #29 to know that that stock i have will be enough for 29 days, not 34

  13. #13
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Counting columns if their sum is equal to no.

    Try:
    Please Login or Register  to view this content.

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

    Re: Counting columns if their sum is equal to no.

    Try using this variation on Leelnich's suggestion to give 29 in C3

    =LOOKUP(B3,SUBTOTAL({9,2},OFFSET(A3,0,0,ROW(A3:A$367)-ROW(A3)+1)))
    Audere est facere

  15. #15
    Registered User
    Join Date
    10-20-2017
    Location
    Egypt
    MS-Off Ver
    Microsoft office 2010
    Posts
    15

    Re: Counting columns if their sum is equal to no.

    Thanks a lot guys,

    Both of them worked OK....am sorry for interruption and my mismatch

    Small concern..can you tell me the best way to understand these techniques??!!

  16. #16
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Counting columns if their sum is equal to no.

    Please Login or Register  to view this content.
    ROW(A3:A$367)-ROW(A3)+1 Returns a numeric array : {1,2,3…,365} (= array_of_heights)
    OFFSET(A3,0,0, array_of_heights) Returns array_of_ranges; each starts on this row and extends downward height cells
    SUBTOTAL(9, array_of_ranges) Returns an array_of_sums, one for each range in array
    INDEX( array_of_sums,) Forces the rest of formula to treat this part as an array
    MATCH(B3,array_of_sums) Returns the position within array of the last sum <= B3 (quantity_available)
    OFFSET(A3,0,0,height (= position)) Returns the range extending from this row to the calculated position.
    COUNT(calculated_range) Returns the number of numeric values in calculated_range (= # of days).
    Last edited by leelnich; 12-26-2017 at 07:31 PM.

+ 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. Counting color cells that has value of less or equal to 2
    By yankiessg in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-12-2016, 01:30 PM
  2. Counting adjacent cells(text) to equal one value
    By Calithea in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2012, 04:08 AM
  3. Counting equal numbers
    By nitrone in forum Excel General
    Replies: 3
    Last Post: 10-06-2011, 08:21 AM
  4. Counting values greater than or equal to those in another column
    By andyr85 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-26-2010, 04:15 PM
  5. Counting last 10 values in a list equal to W or L with updating
    By ablocki1988 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-14-2008, 02:06 PM
  6. counting the number of equal rows
    By Malte Lindner in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-15-2006, 04:55 PM
  7. [SOLVED] counting the number of equal rows
    By Malte in forum Excel General
    Replies: 1
    Last Post: 04-15-2006, 12: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