Samgraphics
Board Regular
- Joined
- Jan 9, 2022
- Messages
- 52
- Office Version
- 2011
- Platform
- MacOS
So I don’t know if this is possible or if this is an effective way to do this or if this helps really. I’m using this countif formula to highlight lottery numbers that match from tickets I bought.
=COUNTIF($C$4:$H$4,C10)
In a different worksheet I’m using a method I saw on a MrExcel youtube tutorial to find the most popular numbers using a pivot table and it shows you the numbers that are the most popular and it can show you by year, month, quarter, and so on, which got me thinking of looking at the numbers that usually match on the tickets I bought. I was thinking it would show me the most popular matched numbers from my tickets. You never know, there might be something there, or not, just wondering how you would go about doing that or if there’s a way to do that.
Below is. a snippet of the sheet that highlights the lottery number matches and the table that shows the most popular numbers. Is there a way I can combine the two so that I can find the most popular numbers that get matches from the tickets I buy?
It didn't include the slicer and other stuff.
I don’t know if I’m being clear enough. Basically, after the countif formula shows me all the numbers that match if there was a way to put all that in a pivot table to be able to see which of my numbers usually hit. I know it’s more or less the same as the most popular numbers pivot table but it’s of the data of all the winning numbers over a period of time. I wanted to see a similar thing but only with the numbers from my tickets that matched.
I was wondering if anyone can please help me see/find a way to do that?
Thank you so much
=COUNTIF($C$4:$H$4,C10)
In a different worksheet I’m using a method I saw on a MrExcel youtube tutorial to find the most popular numbers using a pivot table and it shows you the numbers that are the most popular and it can show you by year, month, quarter, and so on, which got me thinking of looking at the numbers that usually match on the tickets I bought. I was thinking it would show me the most popular matched numbers from my tickets. You never know, there might be something there, or not, just wondering how you would go about doing that or if there’s a way to do that.
Below is. a snippet of the sheet that highlights the lottery number matches and the table that shows the most popular numbers. Is there a way I can combine the two so that I can find the most popular numbers that get matches from the tickets I buy?
| MOST POPULAR NUMBERS UPDATED.xlsx | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
| 1 | ||||||||||||||||||||||||
| 2 | YEAR | 2022 | ||||||||||||||||||||||
| 3 | ||||||||||||||||||||||||
| 4 | Count of TYPE | |||||||||||||||||||||||
| 5 | Row Labels | Total | ||||||||||||||||||||||
| 6 | 3 | 4 | ||||||||||||||||||||||
| 7 | 5 | 2 | ||||||||||||||||||||||
| 8 | 6 | 1 | ||||||||||||||||||||||
| 9 | 4 | 1 | ||||||||||||||||||||||
| 10 | 8 | 1 | ||||||||||||||||||||||
| 11 | 7 | 1 | ||||||||||||||||||||||
| 12 | 2 | 1 | ||||||||||||||||||||||
| 13 | Grand Total | 11 | ||||||||||||||||||||||
Sheet4 | ||||||||||||||||||||||||
It didn't include the slicer and other stuff.
| HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | LOTTERY TICKET PURCHASE HISTORY | ||||||||||
| 2 | |||||||||||
| 3 | LATEST DRAW | ||||||||||
| 4 | NO 1 | NO 2 | NO 3 | NO 4 | NO 5 | BONUS 1 | BONUS 2 | ||||
| 5 | 1 | 4 | 12 | 32 | 34 | 9 | 10 | ||||
| 6 | |||||||||||
| 7 | |||||||||||
| 8 | DATE | NO 1 | NO 2 | NO 3 | NO 4 | NO 5 | BONUS 1 | BONUS 2 | WINNERS | ||
| 9 | 11/22/21 | 1 | 5 | 6 | 7 | 9 | 9 | 10 | 5 | ||
| 10 | 1 | 5 | 7 | 8 | 9 | 7 | 12 | 0 | |||
| 11 | 1 | 9 | 12 | 33 | 34 | 9 | 12 | 15 | |||
LotteryTable | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I9:I11 | I9 | =IFERROR(VLOOKUP(--(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))&SUMPRODUCT(--(G9:H9=TRANSPOSE($G$5:$H$5)))),--$M$26:$P$38,4,0),0) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| number07 | =tbllottery[BONUS 2] | I9 |
| number08 | =tbllottery[BONUS 1] | I9 |
| number09 | =tbllottery[NO 5] | I9 |
| numberCheck | =LotteryTable!$B9:INDEX(number09,COUNT(number09),1) | I9 |
| numberCheck02 | =LotteryTable!$G9:INDEX(number08,COUNT(number08),1) | I9 |
| numberCheck03 | =LotteryTable!$H9:INDEX(number07,COUNT(number07),1) | I9 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| G9:H11 | Expression | =COUNTIF($G$5:$H$5,G9) | text | NO |
| B9:F11 | Expression | =COUNTIF($B$5:$F$5,B9) | text | NO |
| I9:I546 | Expression | =SUMPRODUCT(COUNTIF(G9:H9,$G$5:$H$5))=2 | text | NO |
| I9:I546 | Expression | =SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5)) + SUMPRODUCT(COUNTIF(G9:H9,$G$5:$H$5))>=3 | text | NO |
I don’t know if I’m being clear enough. Basically, after the countif formula shows me all the numbers that match if there was a way to put all that in a pivot table to be able to see which of my numbers usually hit. I know it’s more or less the same as the most popular numbers pivot table but it’s of the data of all the winning numbers over a period of time. I wanted to see a similar thing but only with the numbers from my tickets that matched.
I was wondering if anyone can please help me see/find a way to do that?
Thank you so much
