Need assistance with a formula

E

esmer

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.
 
B

Biff

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
 
B

Bob Phillips

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)
 
D

David Billigmeier

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

Bob Phillips

so you use this sumporduct function too? <vbg>

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
B

Biff

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="ins")

Biff
 
Top