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

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.

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

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.

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.

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.

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

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