Hey, welcome back to the MrExcel netcast,

I’m Bill Jelen. Today’s question comes from Scott in Australia. If you have a question for the Podcast, please

feel free to send it in. You can call and leave it in as a voice mail

in the US, (866) 581-0221; if you have a Skype account, you can leave it in my Skype voice

mail, billjelen; or in London, 020-7871-4957; or just drop me an email, [email protected] Now, Scott has a question today that involves

something very important– I mean work-related– especially if we’re trying to get out of work

by hitting the lottery. Outstanding. Scott’s question is, he has a list of most

frequent numbers that hit in the lottery over time– and we’ll figure out how he got that

later, probably in tomorrow’s Podcast. But, today, each week he puts in the numbers

that came up in the lottery, he wants to know how many times those numbers were in the list

of most frequent numbers. Now, Scott had proposed using a series of

six Countif formulas that were all added together; we can do one better with a single formula

called an array formula. Now, array formulas are very, very powerful

in Excel, and watch as I build this. The first thing I’m going to think about is

that I want to end up with a string of six zeros or ones– a 1 means that the number

was in the list, a 0 means the number was not in the list. Now, the array formula is going to return

those six numbers to me, and then I need to sum those so that way I can get the count

of the number 1. So I’m going to start my formula with=SUM

and then use an IF statement. Now, when I do a match command– a match command

says, “Hey, go look for this number 6 within the list and if there is a match return the

number, if there is no match, return an NA. I’m really interested in the number of NA,

so I’m going to use the function called ISNA– ISNA function– and then I’ll put the MATCH

function. And, usually, we say, “Hey, go match this

number 6 with this list,” but instead, because we’re using array formula today, I’m going

to do something out of the ordinary– I’m going to MATCH this entire string of six numbers–

this range, C4 to H4– with the list of most frequent numbers. There’s my match command, I’ll use a closing

parenthesis for the ISNA. Now, if I get an ISNA from that, that means

that the number is not found and I want to put a 0– that’s my “then” part of the

IF function– otherwise, I want to put a 1. There’s the parentheses to close the IF function;

there’s the parentheses to close the SUM function. Now this big formula, if you’re an Excel pro

you’ll say, “Well, this will never work.” We have to use a secret set of keystrokes

in order to force Excel to know that this is an array formula. You have to hold down Ctrl+Shift and then

press Enter, and, sure enough, Excel tells us that three of the numbers from this week

were in the most frequent numbers. Copy that formula down and you’ll see that

even though these numbers are the most frequent numbers, out of five weeks that Scott sent

me, there was only one week where you would have hit three of the six numbers by betting

those repeatedly. Thanks to Scott for sending in that question. Tomorrow we’ll take a look at a different

lottery related question: How to figure out what the most frequent numbers are. See you tomorrow for another netcast from

MrExcel.

How about a spreadsheet to check your ticket numbers. It can add all the numbers that match on each line of each game, and and tell you how many numbers and supplimentaries you have without you trying to remember the numbers as you check each line!

on the newer version of excel this formula doesnt work. i even copy this sample and formula but it always return "1" value.

=TOPLA(EĞER(EYOKSA(KAÇINCI(A2:V2;$AA$2:$AV$2;0));0;1)) ctrl+shift+enter Thanks to the excellent formula after many years …