Jack-P-Winner
Member
=(COUNTIF(F2:F51,AB3)+COUNTIF(Q2:Q51,AB3)+
What does this code mean?
- Thread starter Jack-P-Winner
- Start date
Somendra Misra
Excel Ninja
Hi Jack,
Your code is wrong as there is an excess ( and +.
If you remove them than this formula will return the sum of count of AB3 in the range F2:F51 & Q2:Q51.
Regards,
Your code is wrong as there is an excess ( and +.
If you remove them than this formula will return the sum of count of AB3 in the range F2:F51 & Q2:Q51.
Regards,
vijay.vizzu
Member
Dear Jack..
The above formula =COUNTIF(F2:F51,AB3)+COUNTIF(Q2:Q51,AB3), lets break it for better understanding
First of all, you should aware the syntax of COUNTIF. The syntax is COUNTIF(Criteria Range, Criteria). Excel will count your criteria from the Given Criteria Range and returns the numerical results or error values. It will not work on any Text results. If we will break the above formula,
COUNTIF(F2:F51,AB3), here the Criteria Range is F2:F51, criteria is AB3. That means the value in AB3, will counted in the Criteria Range, and if found then it will return the numerical result. If not found then it will give 0. Same will be applied for 2nd part i.e COUNTIF(Q2:Q51,AB3). After that both values will be summed up.
In excel 2007 and above, we can use COUNTIFS for multiple Ranges and multiple criteria. And we can also use SUMPRODUCT to achieve this.
I hope, the explanation will clear your all doubts
The above formula =COUNTIF(F2:F51,AB3)+COUNTIF(Q2:Q51,AB3), lets break it for better understanding
First of all, you should aware the syntax of COUNTIF. The syntax is COUNTIF(Criteria Range, Criteria). Excel will count your criteria from the Given Criteria Range and returns the numerical results or error values. It will not work on any Text results. If we will break the above formula,
COUNTIF(F2:F51,AB3), here the Criteria Range is F2:F51, criteria is AB3. That means the value in AB3, will counted in the Criteria Range, and if found then it will return the numerical result. If not found then it will give 0. Same will be applied for 2nd part i.e COUNTIF(Q2:Q51,AB3). After that both values will be summed up.
In excel 2007 and above, we can use COUNTIFS for multiple Ranges and multiple criteria. And we can also use SUMPRODUCT to achieve this.
I hope, the explanation will clear your all doubts
Jack-P-Winner
Member
wow, I had no idea!!! THANK YOU!!
Here is the whole formula and I have many lines of these
=(COUNTIF(C2:C101,F4)+COUNTIF(F2:F101,F4)+COUNTIF(I2:I101,F4)+COUNTIF(L2:L101,F4)+COUNTIF(O2:O101,F4)+COUNTIF(X2:X101,F4)-(COUNTIF(C2:C3,F4)+COUNTIF(F2:F3,F4)+COUNTIF(I2:I3,F4)+COUNTIF(L2:L3,F4)+COUNTIF(O2:O3,F4)+COUNTIF(X2:X3,F4)))
How should I write it?
Here is the whole formula and I have many lines of these
=(COUNTIF(C2:C101,F4)+COUNTIF(F2:F101,F4)+COUNTIF(I2:I101,F4)+COUNTIF(L2:L101,F4)+COUNTIF(O2:O101,F4)+COUNTIF(X2:X101,F4)-(COUNTIF(C2:C3,F4)+COUNTIF(F2:F3,F4)+COUNTIF(I2:I3,F4)+COUNTIF(L2:L3,F4)+COUNTIF(O2:O3,F4)+COUNTIF(X2:X3,F4)))
How should I write it?
Jack-P-Winner
Member
EXCELLENT EXPLANATION!!!!! Thank you so much. I had help writing this massive program that has massive mistakes I am trying to correct. I basically have a number lets say "6" in cell. I need to change it to a 5 or 7. How can I figure out how to change that numeric value?
Here is the book I am working on and this formulas is on tab Lnk and Drawn numbers. Can you look at it and tell me its all wrong LOL or what is incorrect?
http://www.mediafire.com/view/s6orpqybcbbsdua/Game41_NumbersInput_MOD.xlsm
Here is the book I am working on and this formulas is on tab Lnk and Drawn numbers. Can you look at it and tell me its all wrong LOL or what is incorrect?
http://www.mediafire.com/view/s6orpqybcbbsdua/Game41_NumbersInput_MOD.xlsm
vijay.vizzu
Member
Dear Jack..
The provided link, requires login information. So please upload the file directly in this forum, no need for third party data storage sites.
The provided link, requires login information. So please upload the file directly in this forum, no need for third party data storage sites.
Jack-P-Winner
Member
The books are large Small not that big. Large are 2 gigs ea Yes 2 gigs lol
Debraj
Excel Ninja
Jack-P-Winner
Member
I just tried the link. It is active. It is the Macro that is says you can not load.
Jack-P-Winner
Member
Here you go, I made a smaller one
Deepak
Excel Ninja
Jack-P-Winner
Member
I just uploaded the file in post #9
Jack-P-Winner
Member
Were you able to open the file?
NARAYANK991
Excel Ninja
Hi Larry ,
For anyone to determine whether the formula is correct , you will need to explain what you expect the formula to do.
Narayan
For anyone to determine whether the formula is correct , you will need to explain what you expect the formula to do.
Narayan
Jack-P-Winner
Member
NARAYANK991
Excel Ninja
Hi Larry ,
You might not remember that your initial post was showing a portion of the whole formula , and Misra's reply was with reference to that fragment !
There is nothing technically wrong with the complete formula , otherwise Excel would have either rejected it outright , or displayed an error value as the output.
Since it has done neither , all that we can say is that the formula might not be doing what you expect it to do ; but this we cannot say since we do not know what you want it to do ; if you can explain in words what you want the formula to do , then we can confirm whether the formula is correct.
Narayan
You might not remember that your initial post was showing a portion of the whole formula , and Misra's reply was with reference to that fragment !
There is nothing technically wrong with the complete formula , otherwise Excel would have either rejected it outright , or displayed an error value as the output.
Since it has done neither , all that we can say is that the formula might not be doing what you expect it to do ; but this we cannot say since we do not know what you want it to do ; if you can explain in words what you want the formula to do , then we can confirm whether the formula is correct.
Narayan
Jack-P-Winner
Member
Narayan,
I believe this formula does exactly what I am asking it to do, I just need to figure out how to change the number digit in cells ALW. The cells that are red is because the digit is odd and green because the digit is even. I have sever cells with odd numbers displaying green and even numbers displaying odd numbers. This is what I have been trying to fix and explain. Maybe this explains it better idk I have been digesting the conditional format to find it and have had some success with some errors but nothing that has led me how to change this numeric digit(s) in ALW
I believe this formula does exactly what I am asking it to do, I just need to figure out how to change the number digit in cells ALW. The cells that are red is because the digit is odd and green because the digit is even. I have sever cells with odd numbers displaying green and even numbers displaying odd numbers. This is what I have been trying to fix and explain. Maybe this explains it better idk I have been digesting the conditional format to find it and have had some success with some errors but nothing that has led me how to change this numeric digit(s) in ALW
Jack-P-Winner
Member
I will try to explain this a different way and also how some of the sheet works
On all my work I use the stop light theory as it is easy to look at and less on the brain. Green font and gray shade is Even or Up. Red font with yellow shade is either Odd or down... Green always means Even or up. Red always means Odd or down
Let me try to explain a little further and differently. Lets start with the cell W3 in game 40 has an 8. Because the 2 in cell A3 is smaller or went DOWN than the 8 cell W3 AD3 gets a RED cell color. Instead of writing this in a paragraph form I will list below what they should be and how they work
W4 has a 13 and because the 18 in A4 is larger or went UP AC4 gets a green cell
W5 has a 16 and because the 20 in A5 is larger or went UP AC5 gets a green cell
W6 has a 33 and because the 24 in A6 is smaller or went DOWN AD6 gets a red cell
50+ Hit Numbers Even/Odd
W11 has a 12 and A11 has a 8 the number went DOWN so AD11 gets a RED cell
W12 has a 4 and A12 has 14 and the number went UP so AC12 gets a Green cell
W13 has a 9 and A13 has a 17 and the number went UP so AC13 gets a Green cell
W14 has a 6 and A14 has a 12 and the number went UP so AC12 gets a Green cell
I think you have my drift. I was trying really hard to take this a bit at a time. When I reset my sheets the area in the 50+ and OBHITS E-O get jumbled up and some of the cells where I have to place a E or O them get mixed up. I mean it may say it gets a E but I have to place an O
What my issue is I have several cells that should be Red and they are Green and when I place a E in a cell for Even in order for it to work I have to change it to a O in the cells IJ 11-12-13-14-19-20-21-22
The Ups and Downs all work fine TU AE AF
On the Input book, Input tab. The drawn numbers all go down "X" amount of rows (rows 2 thru 101) you tell it to. If I have my Input book full to Game 50 and the draw numbers in row 2 columns IJKLN are 12345. If I reset the game by pressing CTL SHT L that will take all the numbers from rows 2-101 and move them down 1 row deleting the last draw in row 101 and placing the same numbers 12345 which will now appear in Game 49 row 3. If you do the CTL SHFT L again, it will move the 12345 down to game 48 row4 and so on. In the Input tab under columns PQRST you can reset back to different game numbers.
Its apparent that when I reset the games is when it gets out of sequence. I have attached 2 pics. You can see that in Game 50 it is EOEO and 49 is EEEE. When I rest back a game it goes to Game 49 EOEO and then EOEE which this would read EEEE. Still with me?? The counters in the Input book are supposed to match the counters in the external book. Example look at Game 49 in the Input book Row 11 columns CDEF. The counter has 5611 in it Now go to the external book to tab #1A-E-O row3 columns STUV you have 5611. This is how they link together. If you link them go to the last page on the external book and the Input book Input page to link. THATS THE ONLY WAY THEY WILL LINK CORRECTLY. If you look at the other counter numbers form the other rows in the input book 12-13-14 they should all match the tabs in the external book that are labeled the same Row 12 Input book 8572 goes to external book tab #2-E-O. Row 13 Input book 5413 goes to external book tab #3-E-O and so on. so, when I go to reset the books that's when the E-O gets scrambled so to speak. The UD's which are columns NOPQ and YZ AA AB seem to work well. You can look at the E-O page in the external book tab labels Even- Odd Start. Hopefully one of you gurus can help me fix my issues. My brain is like duh after working on this for almost 2 years and I count cells and numbers in my sleep LOL Thanks
On all my work I use the stop light theory as it is easy to look at and less on the brain. Green font and gray shade is Even or Up. Red font with yellow shade is either Odd or down... Green always means Even or up. Red always means Odd or down
Let me try to explain a little further and differently. Lets start with the cell W3 in game 40 has an 8. Because the 2 in cell A3 is smaller or went DOWN than the 8 cell W3 AD3 gets a RED cell color. Instead of writing this in a paragraph form I will list below what they should be and how they work
W4 has a 13 and because the 18 in A4 is larger or went UP AC4 gets a green cell
W5 has a 16 and because the 20 in A5 is larger or went UP AC5 gets a green cell
W6 has a 33 and because the 24 in A6 is smaller or went DOWN AD6 gets a red cell
50+ Hit Numbers Even/Odd
W11 has a 12 and A11 has a 8 the number went DOWN so AD11 gets a RED cell
W12 has a 4 and A12 has 14 and the number went UP so AC12 gets a Green cell
W13 has a 9 and A13 has a 17 and the number went UP so AC13 gets a Green cell
W14 has a 6 and A14 has a 12 and the number went UP so AC12 gets a Green cell
I think you have my drift. I was trying really hard to take this a bit at a time. When I reset my sheets the area in the 50+ and OBHITS E-O get jumbled up and some of the cells where I have to place a E or O them get mixed up. I mean it may say it gets a E but I have to place an O
What my issue is I have several cells that should be Red and they are Green and when I place a E in a cell for Even in order for it to work I have to change it to a O in the cells IJ 11-12-13-14-19-20-21-22
The Ups and Downs all work fine TU AE AF
On the Input book, Input tab. The drawn numbers all go down "X" amount of rows (rows 2 thru 101) you tell it to. If I have my Input book full to Game 50 and the draw numbers in row 2 columns IJKLN are 12345. If I reset the game by pressing CTL SHT L that will take all the numbers from rows 2-101 and move them down 1 row deleting the last draw in row 101 and placing the same numbers 12345 which will now appear in Game 49 row 3. If you do the CTL SHFT L again, it will move the 12345 down to game 48 row4 and so on. In the Input tab under columns PQRST you can reset back to different game numbers.
Its apparent that when I reset the games is when it gets out of sequence. I have attached 2 pics. You can see that in Game 50 it is EOEO and 49 is EEEE. When I rest back a game it goes to Game 49 EOEO and then EOEE which this would read EEEE. Still with me?? The counters in the Input book are supposed to match the counters in the external book. Example look at Game 49 in the Input book Row 11 columns CDEF. The counter has 5611 in it Now go to the external book to tab #1A-E-O row3 columns STUV you have 5611. This is how they link together. If you link them go to the last page on the external book and the Input book Input page to link. THATS THE ONLY WAY THEY WILL LINK CORRECTLY. If you look at the other counter numbers form the other rows in the input book 12-13-14 they should all match the tabs in the external book that are labeled the same Row 12 Input book 8572 goes to external book tab #2-E-O. Row 13 Input book 5413 goes to external book tab #3-E-O and so on. so, when I go to reset the books that's when the E-O gets scrambled so to speak. The UD's which are columns NOPQ and YZ AA AB seem to work well. You can look at the E-O page in the external book tab labels Even- Odd Start. Hopefully one of you gurus can help me fix my issues. My brain is like duh after working on this for almost 2 years and I count cells and numbers in my sleep LOL Thanks
Jack-P-Winner
Member
Jack-P-Winner
Member
This may get real confusing but I am trying to explain what is going on, In my books my 50+ and OBHITS E-O which stands for Even and Odd are getting jumbled when I reset my games back 1 (one) game. The cells all change colors and that's the red and green cells I am speaking about. Here is an upload. It is a bit hard to read but its the best way I think for me to explain what I am fighting. I have loaded up all 50 games and in 1A it starts Game 50. I then push CTL SHT and L to reset back 1 game to Game 49. In cell 1AA is game 49 except I have not change any of my draw numbers from the Input book input tab so It should read the same as A1. I again reset back 1 to Game 48 in cell 1BA and you can see its the same as Game 49. I reset it 1 more game to Game 47 1CAand as you can see it has changed again. If you continue to look all the way down you will see all my games get jumbled up. For example, look at row 4, row 5 and so on. They are messed up bad and I do not know how to fix them. The easiest way I have found to view these is place your cursor on the row so it lights yup the whole row
NARAYANK991
Excel Ninja
Hi Larry ,
I think this is not the way to go about troubleshooting this problem. Your problem of CF is complex because the values in columns EX and EZ are based on a complete sequence of numbers in column EJ ; unless we know how this sequence changes , it will be practically impossible to know from where the problem originates.
In any problem , we need to know the givens ; what is the input , which cannot be changed ? You need to have one file , which is the starting point , and within this file , we need to know what is the bare minimum that is given.
You talk of 50 games , but is your problem starting at any particular game ? If yes , which is it ? If no , can we start from the first game ?
If we start from game 1 , given some unchangeable input , what do you expect as the output ? Is it possible to know this ? Or is it that based on the results of previous games , the result of the current game can change ? If so , can you work out the inputs for a few games , the actual results of those games , and then based on the results , the expected output for a new game ?
Narayan
I think this is not the way to go about troubleshooting this problem. Your problem of CF is complex because the values in columns EX and EZ are based on a complete sequence of numbers in column EJ ; unless we know how this sequence changes , it will be practically impossible to know from where the problem originates.
In any problem , we need to know the givens ; what is the input , which cannot be changed ? You need to have one file , which is the starting point , and within this file , we need to know what is the bare minimum that is given.
You talk of 50 games , but is your problem starting at any particular game ? If yes , which is it ? If no , can we start from the first game ?
If we start from game 1 , given some unchangeable input , what do you expect as the output ? Is it possible to know this ? Or is it that based on the results of previous games , the result of the current game can change ? If so , can you work out the inputs for a few games , the actual results of those games , and then based on the results , the expected output for a new game ?
Narayan
Jack-P-Winner
Member
Narayan,
You ask where the problem starts. Look at the 2 pictures. It is all over the map. When i go back one game all the E's and O's is what is messed up. I am confused because what does understand what the book is for or does have to do with letters not going from one row to the next in the same order example Game 50 is EEOO Game 49 ia OOEE Game 48 is OOOO amd Game 47 EEEE when I rest to 49, 49 should then have EEOO 48 will have OOEE, 47 will have OOOO and 46 now has EEEE
I am trying to understand your question. The Input book is fed from the external book. I am simply trying to take a group of 4 digit counters and set them back 1 game or 2 or 4 and have the same counter numbers and draw numbers follow with the their outputs which would be a O or E or U or D
If I uploaded a video how they work will that help?
You ask where the problem starts. Look at the 2 pictures. It is all over the map. When i go back one game all the E's and O's is what is messed up. I am confused because what does understand what the book is for or does have to do with letters not going from one row to the next in the same order example Game 50 is EEOO Game 49 ia OOEE Game 48 is OOOO amd Game 47 EEEE when I rest to 49, 49 should then have EEOO 48 will have OOEE, 47 will have OOOO and 46 now has EEEE
I am trying to understand your question. The Input book is fed from the external book. I am simply trying to take a group of 4 digit counters and set them back 1 game or 2 or 4 and have the same counter numbers and draw numbers follow with the their outputs which would be a O or E or U or D
If I uploaded a video how they work will that help?
NARAYANK991
Excel Ninja
Hi Larry ,
Can you take it easy , and go slow ? First explain this statement of yours :
Narayan
Can you take it easy , and go slow ? First explain this statement of yours :
Which file are you referring to , which column / cells within the file ?
Narayan
Jack-P-Winner
Member
I just made up those in post 22. The reset I speak of is in the INPUT book INPUT tab. If you push on your key board CTRL SFT and L it will reset the game back 1 game for example game 50 to 49. Everything (all the information) should follow from Game 50 to 49. All the formulas, all the counters EVERYTHING. I am not yelling (; I use caps to emphasize LOL
okay, open the external book. Go to the tab that says 50+ Even - Odd starter. This is the page I speak of when I am saying things like EOEO. Now , in this same book go to the tab #1-A-E-O ( all the tabs are the same way just a different number EVERYTHING) the counters are under columns STUV, these are the same counters that are n the other book. The games are lined out the same way as far as the numbering. Each counter form each page goes into the other book in their perspective cell reference. Game 48 counter for #1 goes from the external book #1 STUV to the Input book Game 48 columns CDEF cells 11-12-13-14. They are all linked together. When I press the restet everything moves so I do not understand why anyone needs to know what each formula is doing and how it thinks. That is why I am confused
okay, open the external book. Go to the tab that says 50+ Even - Odd starter. This is the page I speak of when I am saying things like EOEO. Now , in this same book go to the tab #1-A-E-O ( all the tabs are the same way just a different number EVERYTHING) the counters are under columns STUV, these are the same counters that are n the other book. The games are lined out the same way as far as the numbering. Each counter form each page goes into the other book in their perspective cell reference. Game 48 counter for #1 goes from the external book #1 STUV to the Input book Game 48 columns CDEF cells 11-12-13-14. They are all linked together. When I press the restet everything moves so I do not understand why anyone needs to know what each formula is doing and how it thinks. That is why I am confused
NARAYANK991
Excel Ninja
Hi Larry ,
Please note that the RESET macro does not do all that you have mentioned ; all it does is shift the input numbers on the INPUT tab one row down , and fill up the top most row ( row 2 ) with the symbol "?".
None of the formulae is transferred or changed in any way.
Thus , if the formulae are all correct , then pushing the information down one row may or may not retain all of the statuses the way you expect them to be retained.
That is why I say that we should start from Game 1 , and start building up game after game , till we reach game 50 ; at each stage we take a snapshot of the game sheet and save it in a separate workbook / worksheet.
When we have reached game 50 , now we do the reverse process , again taking a snapshot after each reset.
When we compare the snapshots taken while building up , and while coming down , they should both match for every game ; if not we can take it from that point onwards.
Narayan
Please note that the RESET macro does not do all that you have mentioned ; all it does is shift the input numbers on the INPUT tab one row down , and fill up the top most row ( row 2 ) with the symbol "?".
None of the formulae is transferred or changed in any way.
Thus , if the formulae are all correct , then pushing the information down one row may or may not retain all of the statuses the way you expect them to be retained.
That is why I say that we should start from Game 1 , and start building up game after game , till we reach game 50 ; at each stage we take a snapshot of the game sheet and save it in a separate workbook / worksheet.
When we have reached game 50 , now we do the reverse process , again taking a snapshot after each reset.
When we compare the snapshots taken while building up , and while coming down , they should both match for every game ; if not we can take it from that point onwards.
Narayan