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

--

HTH

Bob Phillips

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

"Sudz" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[[email protected]]

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.

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

--

Regards,

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

<[email protected]> wrote in message
news:[email protected]...
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.

[[email protected]]

RagDyeR wrote:
> <[email protected]> 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.

[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 = "CA01",'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 !
----------------------------------------------------------------------------
-------------------

"Sudz" <[email protected]> wrote in message
news:[email protected]...

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