I need a formula to compute same information for an entire column. For
example -sumporduct(-(a1:a10000="Approved"),-(B1:B10000="Insurance")) this
works for a range of cells I need a formula for an entire column.

Hi!

Use another formula and split the columns in 2:

=sumporduct(--(a1:a30000="Approved"),--(B1:B30000="Insurance"))+sumporduct(--(a30001:a65536="Approved"),--(B30001:B65536="Insurance"))

Biff

"esmer" wrote in message
...
I need a formula to compute same information for an entire column. For
example -sumporduct(-(a1:a10000="Approved"),-(B1:B10000="Insurance")) this
works for a range of cells I need a formula for an entire column.

so you use this sumporduct function too? <vbg

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Biff" wrote in message
...
Hi!

Use another formula and split the columns in 2:


=sumporduct(--(a1:a30000="Approved"),--(B1:B30000="Insurance"))+sumporduct(-
-(a30001:a65536="Approved"),--(B30001:B65536="Insurance"))

Biff

"esmer" wrote in message
...
I need a formula to compute same information for an entire column. For
example -sumporduct(-(a1:a10000="Approved"),-(B1:B10000="Insurance"))
this
works for a range of cells I need a formula for an entire column.

That was the first thing that came to mind.

This is probably better:

=SUMPRODUCT(--(A1:A65535="App"),--(B1:B65535="Ins"))+AND(A65536="app",B65536="ins")

Or:

=SUMPRODUCT(--(A1:A65535="Approved"),--(B1:B65535="Insurance"))+(A65536="app")*(B65536="i ns")

Biff

"Bob Phillips" wrote in message
...
so you use this sumporduct function too? <vbg

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Biff" wrote in message
...
Hi!

Use another formula and split the columns in 2:


=sumporduct(--(a1:a30000="Approved"),--(B1:B30000="Insurance"))+sumporduct(-
-(a30001:a65536="Approved"),--(B30001:B65536="Insurance"))

Biff

"esmer" wrote in message
...
I need a formula to compute same information for an entire column. For
example -sumporduct(-(a1:a10000="Approved"),-(B1:B10000="Insurance"))
this
works for a range of cells I need a formula for an entire column.

Neither SUMPRODUCT nor array formulae can do whole columns. The best you can
do is

=SUMPRODUCT(-(A1:A65535="Approved"),-(B1:B65535="Insurance"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"esmer" wrote in message
...
I need a formula to compute same information for an entire column. For
example -sumporduct(-(a1:a10000="Approved"),-(B1:B10000="Insurance")) this
works for a range of cells I need a formula for an entire column.

Have you tried using pivot tables? Sounds like they could help in your
situation.

--
Regards,
Dave


"esmer" wrote:

I need a formula to compute same information for an entire column. For
example -sumporduct(-(a1:a10000="Approved"),-(B1:B10000="Insurance")) this
works for a range of cells I need a formula for an entire column.