LinkBack URL
About LinkBacks
counting how many colered cells
A4 random numbers 1-36 in a row of 10000 cells
-all the numbers up to 30 are orange, but now I need to find the sums of these fields
pls help, Mare
Posnetek zaslona 2023-07-16 134555.png
123.xlsx FILE DOWNLOAD
Roulette counting how many colored cells are between
counting how many colered cells
A4 random numbers 1-36 in a row of 10000 cells
-all the numbers up to 30 are orange, but now I need to find the sums of these fields
pls help, Mare
Posnetek zaslona 2023-07-16 134555.png
123.xlsx FILE DOWNLOAD
Last edited by 6StringJazzer; 07-16-2023 at 11:04 AM. Reason: typos in title
Welcome to the forum.
Try this:
=SUMIF(A4:A1000,"<="&30,A4:A1000)
You may need to change commas to semi-colons.
Your Excel version is not 11 - that's Windows! Please change your forum profile to show which version of Excel you have. Thanks.
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.
I see 8 in your sreenshot and there are 8 consecutive numbers up to 30.
8 is not the sum of these numbers.
Do you mean how many consecutive numbers up to 30 are there each time?
And do you mean each time or only the first one? I see only 1 value in Column B.
hello, when I enter your formula, it adds up all the orange cells, but really I want it to calculate the sum of the orange fields
how many times the numbers up to 0-30 appear, the order is not important.
And then I wonder how many times this happens up to 1000 random numbers between 0-36
adds up and calculate the sum is the same.
But if you want to count these cells, please tryFormula:
Please Login or Register to view this content.
In your sample sheet are 997 numbers.
If you try this many times the average of these counting should be =31/37*997 is about 835.3
And with 1000 numbers the average sould be about 837.8
Last edited by HansDouwe; 07-16-2023 at 08:58 AM.
it works if I select the orange boxes...
-I changed the document so that the numbers are now fixed.
*the problem is that it is time-consuming to do up to 1000 numbers....
and setting the formula for each sum of cells
>>isn't there some formula that would calculate how many of these fields there are?
and which number is the largest
Do you want this in B4 and copy down:and try for the largest number:Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
you are the king, thank you 100x I've been trying to solve this for a week
Thanks for your feedback, Glad to have helped. .
If you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.
EDIT NOTE: This will probably not work for you. I thought you had XL365 but now see you have XL2019 instead. I don't have that version, so I am not sure if it has dynamic arrays in it or not.
Here is HansDouwe's formula as a single, spilling formula (no need to copy it down), just enter it into cell B4 and it will do the rest...
Formula:Please Login or Register to view this content.
Last edited by Rick Rothstein; 07-16-2023 at 11:47 AM.
I had also thought of that, but MAP does not work for Excel 2019, which is why I omitted that solution.
hi
what would be the formula if i wanted to see numbers up to 24-exclude zeros?
This is not good:
=IF(AND(A4<=24;A3>24);IFNA(MATCH(TRUE;A4:A$1000>24;0)-1;ROWS(A4:A$1000));"")
how can i exclude zeros
Attachment 836617
Thanks for the rep
Up to 24 exclude 0:
Please try in B4 and copy down:Note: If you want attach a workbook, please do this via Go advanced and manage attachments (see yellow banner at the top of this page) otherwhise I get the message "invalid attachment specified" and I can not see the attachment.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks