Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with a formula
This is pretty hard to explain but ill do my best. Im trying to figure out a formula for this problem This is using multiple forms in the Spreadsheet, Form 1: License State License Number Date Issued Date Expired Form 2: This is where i need to formula that i cant figure out. Say i have 50 States, 1,000 License Numbers per state, but of course they have different issue and expiration dates. What i want this formula to do is to give me a total number of Licenses that arent expired. -- Sudz ------------------------------------------------------------------------ Sudz's Profile: http://www.excelforum.com/member.php...o&userid=36083 View this thread: http://www.excelforum.com/showthread...hreadid=558674 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with a formula
This is pretty hard to explain but ill do my best. Im trying to figure
out a formula for this problem This is using multiple forms in the Spreadsheet, Form 1: License State License Number Date Issued Date Expired Form 2: This is where i need to formula that i cant figure out. Say i have 50 States, 1,000 License Numbers per state, but of course they have different issue and expiration dates. What i want this formula to do is to give me a total number of Licenses that aren't expired. One way is to use a helper column. If Form 1 uses columns A, B, C, D, put this in E1 and copy down: =IF(TODAY()=D1,A1,"") So E:E contains the state for each license that's not expired. Then in Form 2 use the COUNTIF function with range E:E. Excel's built-in Help describes the function. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with a formula
=SUMIF(D:D,""&TODAY())
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sudz" wrote in message ... This is pretty hard to explain but ill do my best. Im trying to figure out a formula for this problem This is using multiple forms in the Spreadsheet, Form 1: License State License Number Date Issued Date Expired Form 2: This is where i need to formula that i cant figure out. Say i have 50 States, 1,000 License Numbers per state, but of course they have different issue and expiration dates. What i want this formula to do is to give me a total number of Licenses that arent expired. -- Sudz ------------------------------------------------------------------------ Sudz's Profile: http://www.excelforum.com/member.php...o&userid=36083 View this thread: http://www.excelforum.com/showthread...hreadid=558674 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with a formula
Sudz wrote:
This is pretty hard to explain but ill do my best. You did a good job of it. This is using multiple forms in the Spreadsheet, Form 1: License State License Number Date Issued Date Expired [....] This is where i need to formula that i cant figure out. Say i have 50 States, 1,000 License Numbers per state, but of course they have different issue and expiration dates. What i want this formula to do is to give me a total number of Licenses that arent expired. One approach, assuming "Date Expired" is column D and data starts in row 2, enter the following array formula (type ctrl-shift-Enter instead of Enter): =count(if(D2:D50001=today(),1,FALSE)) Note: You can omit ",FALSE" since that is the default. But so many people incorrectly omit the 3rd parameter when they shouldn't and run into trouble that I think it is a good idea to always specify the 3rd parameter. It's a matter of taste/style. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with a formula
I believe that Bob's non-array formula is probably the most preeminent, even
though he temporarily forgot that COUNT is *not* spelled "SUM".<g =COUNTIF(D:D,""&TODAY()) -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- wrote in message ups.com... Sudz wrote: This is pretty hard to explain but ill do my best. You did a good job of it. This is using multiple forms in the Spreadsheet, Form 1: License State License Number Date Issued Date Expired [....] This is where i need to formula that i cant figure out. Say i have 50 States, 1,000 License Numbers per state, but of course they have different issue and expiration dates. What i want this formula to do is to give me a total number of Licenses that arent expired. One approach, assuming "Date Expired" is column D and data starts in row 2, enter the following array formula (type ctrl-shift-Enter instead of Enter): =count(if(D2:D50001=today(),1,FALSE)) Note: You can omit ",FALSE" since that is the default. But so many people incorrectly omit the 3rd parameter when they shouldn't and run into trouble that I think it is a good idea to always specify the 3rd parameter. It's a matter of taste/style. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with a formula
RagDyeR wrote:
wrote: enter the following array formula (type ctrl-shift-Enter instead of Enter): =count(if(D2:D50001=today(),1,FALSE)) I believe that Bob's non-array formula is probably the most preeminent, even though he temporarily forgot that COUNT is *not* spelled "SUM".<g =COUNTIF(D:D,""&TODAY()) I concur: a non-array formula trumps an array formula as long it is not an arcane use of SUMPRODUCT ;-). I do wish Excel were consistent in its interpretation of D:D. It did not work (have the desired result) with my array formula. So I was (pleasantly) surprised that it worked in Bob's formula. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with a formula
Thanks for all the help so far guys, its helped a little. This is the actual formula im trying to fix =SUMIF('Input Drivers State'!A12:I1336,AND('Input Drivers State’!B12:B1336 = "PE96",'Input Drivers State'!I13:I1336 ='License Numbers'!B37),'Input Drivers State'!G12:G1336) -- Sudz ------------------------------------------------------------------------ Sudz's Profile: http://www.excelforum.com/member.php...o&userid=36083 View this thread: http://www.excelforum.com/showthread...hreadid=558674 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with a formula
What's in G12 to G1336?
Is it text data (alpha/numeric plate numbers), or what? Better yet, describe what type of data is in each of your columns. Tells us if we can use SumProduct, or do we need maybe Index&Match. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Sudz" wrote in message ... Thanks for all the help so far guys, its helped a little. This is the actual formula im trying to fix =SUMIF('Input Drivers State'!A12:I1336,AND('Input Drivers State’!B12:B1336 = "PE96",'Input Drivers State'!I13:I1336 ='License Numbers'!B37),'Input Drivers State'!G12:G1336) -- Sudz ------------------------------------------------------------------------ Sudz's Profile: http://www.excelforum.com/member.php...o&userid=36083 View this thread: http://www.excelforum.com/showthread...hreadid=558674 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |