Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Similar to Q re 2 Countifs, but Sumproduct doesn't work
I am using this formula to count the number of times that a column M is
=0.5% in the prior 100 days (given as the Referce L$23), only if there is a matching "type" in column K. SUMPRODUCT((OFFSET(K124,1-L$23,,L$23,1)=K125*(OFFSET(M124,1-L$23,,L$23,1)=V20))) But this is not working. I also tried: COUNTIF(OFFSET(K124,1-L$23,,L$23,1),K125-(COUNTIF(OFFSET(M124,1-L$23,,L$23,1),"<.005")))) But this did not work either. Both formulas returned a value of "0" when it should not be 0. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Similar to Q re 2 Countifs, but Sumproduct doesn't work
No idea what is in all those cells, but maybe =SUMPRODUCT((OFFSET(K124,1-L$23,,L$23,1)=K125)*(OFFSET(M124,1-L$23,,L$23,1)=V20)) -- __________________________________ HTH Bob "jtujague" wrote in message ... I am using this formula to count the number of times that a column M is =0.5% in the prior 100 days (given as the Referce L$23), only if there is a matching "type" in column K. SUMPRODUCT((OFFSET(K124,1-L$23,,L$23,1)=K125*(OFFSET(M124,1-L$23,,L$23,1)=V20))) But this is not working. I also tried: COUNTIF(OFFSET(K124,1-L$23,,L$23,1),K125-(COUNTIF(OFFSET(M124,1-L$23,,L$23,1),"<.005")))) But this did not work either. Both formulas returned a value of "0" when it should not be 0. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
sumproduct & Dates & similar Data | Excel Discussion (Misc queries) | |||
Sumproduct & Dates & Similar Data | Excel Worksheet Functions | |||
Help with sumproduct or similar type of function | Excel Discussion (Misc queries) | |||
SUMPRODUCT is OK for a while and later a similar formula returns 0 | Excel Worksheet Functions |