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
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
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.
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
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
It didn't work,
attached a file with the required, thanks in advance for your help. i hope you can help me
And here's a version that works for greater-than-or equal-to :Please Login or Register to view this content.
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.
Thanks alot,
but can you rewrite the formula for the attached sheet as i can't update it to match
Is this what you're after (paste in C3 and copy down)?Please Login or Register to view this content.
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
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.
i am want to use the #29 to know that that stock i have will be enough for 29 days, not 34
Try:Please Login or Register to view this content.
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
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??!!
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks