HELP with a formula

[Sudz]

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.

 

[MyVeryOwnSelf]

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.

 

[Bob Phillips]

=SUMIF(D,">"&TODAY())

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[joeu2004]

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(D250001>=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.

 

[RagDyeR]

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,">"&TODAY())

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

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(D250001>=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.

 

[joeu2004]

enter the following array formula (type ctrl-shift-Enter instead
of Enter): =count(if(D250001>=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,">"&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. It did not work (have the desired
result) with my array formula. So I was (pleasantly) surprised that it
worked in Bob's formula.

 

[Sudz]

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)

 

[RagDyeR]

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 !
----------------------------------------------------------------------------
-------------------


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)