Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countifs equiv in 2003 | Excel Discussion (Misc queries) | |||
Alternative for =countifs (in 2007) for Excel 2003 | Excel Discussion (Misc queries) | |||
Converting COUNTIFS to 2003 format | Excel Worksheet Functions | |||
COUNTIFS for 2003 | Excel Worksheet Functions | |||
excel's new countifs...on 2003? | Excel Discussion (Misc queries) |