Assume data is in range A3:E10.  In A4:A10, various service types are mentioned.  In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012.  In range B4:E10 are numbers representing number of customers.  One may want to answer the following questions from this data:

1. For every date, total number of customer walkins and total number of services taken; and
2. For every date, new customer walkins and new services taken; and
3. For every date, repeat customer walkins and repeat services taken

While the first and third questions are fairly straight forward to solve, some deliberation would be required for the second question.  A new service type taken on June 3, 2012 would be one that has not been taken by any customer from June 1 – 2, 2012.  So if cell A8 has Service type E and cell D8 (data for June 3, 2012) has 3 (3 customer took service type E on June 3, 2012), then this service should be counted only if there is no figure in range B8:C8 i.e. no customer took this service on June 1 – June 2, 2012.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in and the PowerPivot add-in, then a few simple steps and minimal DAX formulas can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system’s performance.

You may refer to my solution in the this workbook.

• Gary's Student says:

If am trying to find an easy way, without using VBA, to determine if two rows have the same values. We have data in B2 thru AF1131. Most of the cells are empty, some have text values. There is no numerical data, there are no formulas. For example, we would like to place an “X” in AG100 if the data in cells B100 thru AF100 match cell-by-cell the data in a previous row.

I can do this easily using VBA using nested loops, but in this case VBA is not an option.

Any suggestions ??

• Hi,

Try this formula in cell AG3

=IF(SUMPRODUCT(1*(MMULT(1*(INDEX(B\$2:AF2=B3:AF3,,)),ROW(\$1:\$31))=496))>0,"X","")

Hope this helps.

• Gary's Student says:

I will give this a try Monday morning.
Thank you

• Ankur says:

Hi Ashish,
can you help me to calculated financial year wise YTD, QTD sales by power pivot?

• Hi,