State Insp Date
FL 1/01/09
SC 1/10/09
NC 1/5/09
FL 2/14/09
VA
FL
GA 1/12/09

Hi, I need help on a formula that will count number of non-blank cells in a
Insp Date column if the State = FL. The result here should be 2.

Thanks!

Try this:

=SUMPRODUCT(--(A2:A8="FL"),--(B2:B8<""))

--
Biff
Microsoft Excel MVP


"Brent" wrote in message
...
State Insp Date
FL 1/01/09
SC 1/10/09
NC 1/5/09
FL 2/14/09
VA
FL
GA 1/12/09

Hi, I need help on a formula that will count number of non-blank cells in
a
Insp Date column if the State = FL. The result here should be 2.

Thanks!

The formula returns the following error #NUM! Any suggestions?

=SUMPRODUCT(--('2009 Inspections'!B:B="FL"),--('2009 Inspections'!E:E<""))


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A2:A8="FL"),--(B2:B8<""))

--
Biff
Microsoft Excel MVP


"Brent" wrote in message
...
State Insp Date
FL 1/01/09
SC 1/10/09
NC 1/5/09
FL 2/14/09
VA
FL
GA 1/12/09

Hi, I need help on a formula that will count number of non-blank cells in
a
Insp Date column if the State = FL. The result here should be 2.

Thanks!

You can't use full-column references with SUMPRODUCT if you are using
Excel 2003 or earlier. Change it to something like:

=SUMPRODUCT(--('2009 Inspections'!B2:B5000="FL"),--('2009 Inspections'!
E2:E5000<""))

or however many rows of data that you have.

Hope this helps.

Pete

On Feb 19, 1:07*pm, Brent wrote:
The formula returns the following error #NUM! Any suggestions?

=SUMPRODUCT(--('2009 Inspections'!B:B="FL"),--('2009 Inspections'!E:E<""))



"T. Valko" wrote:
Try this:

=SUMPRODUCT(--(A2:A8="FL"),--(B2:B8<""))

--
Biff
Microsoft Excel MVP

"Brent" wrote in message
...
State * *Insp Date
FL * * * * *1/01/09
SC * * * * *1/10/09
NC * * * * *1/5/09
FL * * * * *2/14/09
VA
FL
GA * * * * 1/12/09

Hi, I need help on a formula that will count number of non-blank cells in
a
Insp Date column if the State = FL. The result here should be 2.

Thanks!- Hide quoted text -

- Show quoted text -