software

I need a formula to compute same information for an entire column. For
example -sumporduct(-(a1:a10000=quot;Approvedquot;),-(B1:B10000=quot;Insurancequot;)) 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=quot;Approvedquot;),--(B1:B30000=quot;Insurancequot;)) sumporduct(--(a30001:a65536=quot;Approvedquot;),--(B30001:B65536=quot;Insurancequot;))

Biff

quot;esmerquot; gt; wrote in message
...
gt;I need a formula to compute same information for an entire column. For
gt; example -sumporduct(-(a1:a10000=quot;Approvedquot;),-(B1:B10000=quot;Insurancequot;)) this
gt; 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=quot;Approvedquot;),-(B1:B65535=quot;Insurancequot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;esmerquot; gt; wrote in message
...
gt; I need a formula to compute same information for an entire column. For
gt; example -sumporduct(-(a1:a10000=quot;Approvedquot;),-(B1:B10000=quot;Insurancequot;)) this
gt; 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,
Davequot;esmerquot; wrote:

gt; I need a formula to compute same information for an entire column. For
gt; example -sumporduct(-(a1:a10000=quot;Approvedquot;),-(B1:B10000=quot;Insurancequot;)) this
gt; works for a range of cells I need a formula for an entire column.

so you use this sumporduct function too? lt;vbggt;

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Use another formula and split the columns in 2:
gt;
gt;
=sumporduct(--(a1:a30000=quot;Approvedquot;),--(B1:B30000=quot;Insurancequot;)) sumporduct(-
-(a30001:a65536=quot;Approvedquot;),--(B30001:B65536=quot;Insurancequot;))
gt;
gt; Biff
gt;
gt; quot;esmerquot; gt; wrote in message
gt; ...
gt; gt;I need a formula to compute same information for an entire column. For
gt; gt; example -sumporduct(-(a1:a10000=quot;Approvedquot;),-(B1:B10000=quot;Insurancequot;))
this
gt; gt; works for a range of cells I need a formula for an entire column.
gt;
gt;
That was the first thing that came to mind.

This is probably better:

=SUMPRODUCT(--(A1:A65535=quot;Appquot;),--(B1:B65535=quot;Insquot;)) AND(A65536=quot;appquot;,B65536=quot;insquot;)

Or:

=SUMPRODUCT(--(A1:A65535=quot;Approvedquot;),--(B1:B65535=quot;Insurancequot;)) (A65536=quot;appquot;)*(B65536=quot;i nsquot;)

Biff

quot;Bob Phillipsquot; gt; wrote in message
...
gt; so you use this sumporduct function too? lt;vbggt;
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt; Hi!
gt;gt;
gt;gt; Use another formula and split the columns in 2:
gt;gt;
gt;gt;
gt; =sumporduct(--(a1:a30000=quot;Approvedquot;),--(B1:B30000=quot;Insurancequot;)) sumporduct(-
gt; -(a30001:a65536=quot;Approvedquot;),--(B30001:B65536=quot;Insurancequot;))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;esmerquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I need a formula to compute same information for an entire column. For
gt;gt; gt; example -sumporduct(-(a1:a10000=quot;Approvedquot;),-(B1:B10000=quot;Insurancequot;))
gt; this
gt;gt; gt; works for a range of cells I need a formula for an entire column.
gt;gt;
gt;gt;
gt;
gt;