Sat 09 Jan 2016

Excel Quiz 29 – COUNT Quiz – Part 2

By |Saturday, January 09th, 2016|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz 29 - COUNT Quiz - Part II

A quiz on various functions used to perform counting in Excel.

Sat 02 Jan 2016

Article 33 - Rank when Duplicates Exist (Ties)

By |Saturday, January 02nd, 2016|Categories: Articles|Tags: , , , , , |1 Comment

Ranking when duplicates (ties) exist is an interesting problem and you will be called upon to make choices when duplicates exist and you have to rank them. Suppose, you have the data like below. 2 rows are formatted in Yellow and 3 rows are formatted in Green to demonstrate the existence of duplicates.

1

(more…)

Sun 25 Oct 2015

Solution - Challenge 26 – Find Sum given multiple OR conditions across columns

By |Sunday, October 25th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge Challenge 26 – Find Sum given multiple OR conditions across columns.

Put following formula for the result -

=SUMPRODUCT((COUNTIF($F$1:$I$1,$A$2:$A$20)+
COUNTIF($F$2:$I$2,$B$2:$B$20)>0)*($C$2:$C$20))

The workbook illustrating the solution can be downloaded from Solution - Challenge 26 – Find Sum given multiple OR conditions across columns.

Sat 17 Oct 2015

Tips & Tricks 114 - COUNTIF for non-contiguous range

By |Saturday, October 17th, 2015|Categories: Tips and Tricks|Tags: |0 Comments

All of us love COUNTIF. And it is very easy to do - just say =COUNTIF("A1:A100",">5") and it finds all the values within the range A1 to A100 which are greater than 5. But what if I wanted the result for only A3, A8 and it should omit other cells. Try putting in following formula -

=COUNTIF((A3, A8),">5") and it will give you #VALUE error.

A possible solution is

=(A3>5)+(A8>5)

What happens if you need to do for A3, A4, A5, A8, A24, A40, A45, A89. Now, you will have to use a formula like -

=(A3>5)+(A4>5)+(A5>5)+(A8>5)+(A24>5)+(A40>5)+(A45>5)+(A89>5)

The formula becomes cumbersome as the number of cells increase. In this case, you can use below formula. This single formula can take care of contiguous (like A3:A5) and non-contiguous ranges both -

=SUM(COUNTIF(INDIRECT({"A3:A5","A8","A24","A40","A45","A89"}),">5"))

Sat 26 Sep 2015

Challenge 26 - Find Sum given multiple OR conditions across columns

By |Saturday, September 26th, 2015|Categories: Challenges|Tags: , , , , |1 Comment

-- The Excel file related to this challenge can be downloaded from Challenge - Multiple OR Conditions ---

Given your data below, you need to find the total sales for cities given in F1 to I1 and zones given in F2 to I2. Either city has to match in F1 to I1 or zone should match in F2 to I2. If both city and zone match, it should be considered once only not twice.

Seattle East 20
Seatltle West 10
Miami East 30
Denver South 50

The answer for above for Seattle and East should be 20+10+30 = 60 not 20+20+10+30 = 80

Multiple OR Conditions

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 25-Oct-15.

Sat 11 Jul 2015

Excel Quiz 16 - COUNT Quiz - Part I

By |Saturday, July 11th, 2015|Categories: Quizzes|Tags: , , , , , , |0 Comments

Excel Quiz 16 - COUNT Quiz - Part I

Quiz on various COUNT functions provided by Excel. This is part I of II for COUNT Functions quizzes. This quiz covers only those Excel functions which start with the word COUNT.

Sat 10 Jan 2015

Tips and Tricks 73 - Use Conditional Formatting to Highlight Duplicate Cells

By |Saturday, January 10th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

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

1

3, Click on Format Button to format the cells accordingly.

Sat 14 Jun 2014

Tips & Tricks 14 - Count Cells Starting (or Ending) with a particular String

By |Saturday, June 14th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

1. Say you want to count all cells starting with C

=COUNTIF(A1:A10,"c*")

c* is case insensitive. Hence, it will count cells starting with both c or C.

Suppose you want to find all cells starting with Excel.

=COUNTIF(A1:A10,"excel*")

2. For ending

=COUNTIF(A1:A10,"*c")

c* is case insensitive. Hence, it will count cells starting with both c or C.

Suppose you want to find all cells starting with Excel.

=COUNTIF(A1:A10,"*excel")