From: nelly on 13 Apr 2010 05:38 Hi Guys I need an array formula which counts the number of instances range Comp1 has a value greater then zero AND Range Comp2 has a blank value. Sounds simple but I just can't figure it out. Also would be helpfull would the same criterior but instead of counting the instances sums the vales in range Comp1 A B 1 1 3 1 2 1 1 1 4 4 Answer for formula 1 would be 2 and for formula 2 would be 4 Regards Nelly
From: Mike H on 13 Apr 2010 05:50 Hi, Q1 =SUMPRODUCT((A1:A6>0)*(B1:B6="")) Q2 =SUMPRODUCT((A1:A6)*(B1:B6="")) If you are using named ranges then substitute those for my ranges  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "nelly" wrote: > Hi Guys > > I need an array formula which counts the number of instances range Comp1 has > a value greater then zero AND Range Comp2 has a blank value. > > Sounds simple but I just can't figure it out. > > Also would be helpfull would the same criterior but instead of counting the > instances sums the vales in range Comp1 > > A B > 1 1 > 3 > 1 2 > 1 1 > 1 > 4 4 > > Answer for formula 1 would be 2 > > and for formula 2 would be 4 > > Regards > Nelly
From: Jacob Skaria on 13 Apr 2010 05:51 Try 'formula1 =SUMPRODUCT((A1:A10>0)*(B1:B10="")) 'formula 2 =SUMPRODUCT((B1:B10="")*(A1:A10)) OR =SUMIF(B1:B10,"",A1:A10)  Jacob (MVP  Excel) "nelly" wrote: > Hi Guys > > I need an array formula which counts the number of instances range Comp1 has > a value greater then zero AND Range Comp2 has a blank value. > > Sounds simple but I just can't figure it out. > > Also would be helpfull would the same criterior but instead of counting the > instances sums the vales in range Comp1 > > A B > 1 1 > 3 > 1 2 > 1 1 > 1 > 4 4 > > Answer for formula 1 would be 2 > > and for formula 2 would be 4 > > Regards > Nelly
From: Stefi on 13 Apr 2010 05:54 No need for array formulae, these are normal ones: =SUMPRODUCT((A2:A7>0),(ISBLANK(B2:B7))) =SUMPRODUCT((A2:A7>0),(ISBLANK(B2:B7)),A2:A7) Adjust ranges!  Regards! Stefi „nelly” ezt írta: > Hi Guys > > I need an array formula which counts the number of instances range Comp1 has > a value greater then zero AND Range Comp2 has a blank value. > > Sounds simple but I just can't figure it out. > > Also would be helpfull would the same criterior but instead of counting the > instances sums the vales in range Comp1 > > A B > 1 1 > 3 > 1 2 > 1 1 > 1 > 4 4 > > Answer for formula 1 would be 2 > > and for formula 2 would be 4 > > Regards > Nelly
From: RonaldoOneNil on 13 Apr 2010 05:55 Formula 1 =SUMPRODUCT((A1:A6>0)*ISBLANK(B1:B6)) Formula 2 =SUMPRODUCT((A1:A6>0)*ISBLANK(B1:B6)*(A1:A6)) "nelly" wrote: > Hi Guys > > I need an array formula which counts the number of instances range Comp1 has > a value greater then zero AND Range Comp2 has a blank value. > > Sounds simple but I just can't figure it out. > > Also would be helpfull would the same criterior but instead of counting the > instances sums the vales in range Comp1 > > A B > 1 1 > 3 > 1 2 > 1 1 > 1 > 4 4 > > Answer for formula 1 would be 2 > > and for formula 2 would be 4 > > Regards > Nelly

Next

Last
Pages: 1 2 Prev: updating cell range depending on input value Next: Hyperlink won't open in excel 