Suppose you have a grid like below. The challenge before you is to count number of rows which have only single 1. See the grid, the yellow highlighted ones are containing 1 only once. The answer is 3 for this problem. You have to give me a single formula for the same. The relevant Excel…
Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String
Last challenge was about giving a non-array formula for Sum of Square of the Digits in a Numeric String. Now, the challenge is for an alphanumeric string. This time, there is no restriction that you need to give only non-array formula only. You may give array formula also. Let's say cell A1 contains an alphanumeric…
Article 9 – Overcome WildCard VLOOKUP / MATCH Problem when Target String is more than 255 Characters
Once again, I have got idea to write this article after I responded to this post Vlookup to find URL using wildcard in Excel Microsoft Community. Before I delve into problem statement, I want to start with some basic information. Maximum column width can be of 255 characters, this means that if I select a…
Tips and Tricks 73 – Use Conditional Formatting to Highlight Duplicate Cells
Suppose your data is in range A2:A100 and you want to highlight all those cells which are duplicates. 1. Select A2:A100 and Home Tab > Conditional Formatting > New Rule 2. Put following formula after clicking "Use a formula to determine which cells to format" =COUNTIF($A$2:$A$100,A2)>1 3, Click on Format Button to format the cells…
Tips & Tricks 72 – Sum Every Nth Row
If your numbers are in range A1:A100, use below formula =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) Above formula is for every 2nd row. Replace 2 with N. Hence, for every 5th row – =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0)) This is a generic formula and will work for any range. If you range is B7:B50, your formula would become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
Excel Quiz 5
Tips & Tricks 71 – Sum Every Odd Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)<>0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)<>0))
Tips & Tricks 70 – Sum Every Even Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
Excel Quiz 4
Tips & Tricks 69 – Sum Top N values in a Range
Suppose you have numbers in range A1:A100 and you want to sum up top N values =SUMPRODUCT(LARGE($A$1:$A$100,ROW(1:10))) In case, you want to ignore 0 values (and blanks) =SUMPRODUCT(LARGE(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10))) Both the above formulas will function only if there are at least N values as per ROW(1:N). Hence, for above formulas, it would work only if there…