Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr
 

I may have spoke too soon on the prior thread, as I thought the sumproduct
solution worked on all my tabs but it did not. Anyway, I did get the countifs
to work correctly as shown below. But what I need to do is to convert the
below solutions/formulas to sumproducts so they'll work with 2003. Here are
the formulas I have in cells N1, N2 & N3:

=COUNTIFS($P$13:$P$100,"=-100",$P$13:$P$100,"<=100",$P$13:$P$100,"<")

=COUNTIFS($P$13:$P$100,"=-400",$P$13:$P$100,"<=400",$P$13:$P$100,"<")-N1

=COUNT($P$13:$P$100)-SUM(N1:N2)

Thanks in advance for everyone's patience with me.

Steve

Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr
 

=SUMPRODUCT(--(($P$13:$P$100=-100)--(,$P$13:$P$100<=100),--($P$13:$P$100<""))

=SUMPRODUCT(--($P$13:$P$100=-400).--($P$13:$P$100<=400),--($P$13:$P$100<""))-N1

--
__________________________________
HTH

Bob

"Steve" wrote in message
...
I may have spoke too soon on the prior thread, as I thought the sumproduct
solution worked on all my tabs but it did not. Anyway, I did get the
countifs
to work correctly as shown below. But what I need to do is to convert the
below solutions/formulas to sumproducts so they'll work with 2003. Here
are
the formulas I have in cells N1, N2 & N3:

=COUNTIFS($P$13:$P$100,"=-100",$P$13:$P$100,"<=100",$P$13:$P$100,"<")

=COUNTIFS($P$13:$P$100,"=-400",$P$13:$P$100,"<=400",$P$13:$P$100,"<")-N1

=COUNT($P$13:$P$100)-SUM(N1:N2)

Thanks in advance for everyone's patience with me.

Steve

Countifs to Sumproduct for 2003 - was Simple Countifs.. fro
 

Please ignore my prior response (if it shows up). The below worked perfectly,
with a couple of changes to parenthese and comma placements.

These are the corrected formulas that worked:

=SUMPRODUCT(--($P$13:$P$57<=100),--($P$13:$P$57=-100),--($P$13:$P$57<""))

=SUMPRODUCT(--($P$13:$P$57<=400),--($P$13:$P$57=-400),--($P$13:$P$57<""))-N1

Thanks again,

"Bob Phillips" wrote:

=SUMPRODUCT(--(($P$13:$P$100=-100)--(,$P$13:$P$100<=100),--($P$13:$P$100<""))

=SUMPRODUCT(--($P$13:$P$100=-400).--($P$13:$P$100<=400),--($P$13:$P$100<""))-N1

--
__________________________________
HTH

Bob

"Steve" wrote in message
...
I may have spoke too soon on the prior thread, as I thought the sumproduct
solution worked on all my tabs but it did not. Anyway, I did get the
countifs
to work correctly as shown below. But what I need to do is to convert the
below solutions/formulas to sumproducts so they'll work with 2003. Here
are
the formulas I have in cells N1, N2 & N3:

=COUNTIFS($P$13:$P$100,"=-100",$P$13:$P$100,"<=100",$P$13:$P$100,"<")

=COUNTIFS($P$13:$P$100,"=-400",$P$13:$P$100,"<=400",$P$13:$P$100,"<")-N1

=COUNT($P$13:$P$100)-SUM(N1:N2)

Thanks in advance for everyone's patience with me.

Steve