fbpx

All articles with 'FILTER()' Tag

Excel Dynamic Array Functions – What are they, how to use them, Examples and FAQs

Published on Apr 6, 2020 in Learn Excel
Excel Dynamic Array Functions – What are they, how to use them, Examples and FAQs

Excel Dynamic Array Functions are a true game changer. These newly introduced DA functions can filter, sort, remove duplicates and do much more. The output of these functions can go to a range of cells. Hence the name – dynamic array functions.

Continue »

Distinct Count & Blanks – Power Pivot Real Life Example

Published on Feb 11, 2013 in Power Pivot
Distinct Count & Blanks – Power Pivot Real Life Example

When it comes to analyzing business data, managers are always asking, “so how many distinct x each y is doing?”

And that sends us, data analysts & reporting professionals running from pillar to post figuring out the best way to do it.

  • We can use variations of SUMPRODUCT, COUNTIFS etc, but the methods are not flexible..
  • We can use VBA, but it would become slow as you add more data.
  • We can use Pivot tables, but it only gives half of what we want ie each y part, but not distinct count of x.
  • We might as well shave our head with a shovel before manually counting values.

And that brings us to 2 distinctly simple solutions:

  • Using Power Pivot & Excel 2010
  • Using regular pivot tables in Excel 2013

Today, lets talk about these 2 approaches & see why they are so better than anything else for distinct count situations.

Continue »