SUMPRODUCT

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Excel Tools and News from the web

I have had a bunch of pretty cool Excel things to post up – and finally got around to clearing my flagged items and browser windows.

Conditional Formatting

excel 2007I have written a couple of articles on Conditional formatting in Excel 2007 with lots of readers comments and requests for help. They are the two most read articles on this site.

I was very interested then to come across this article on Joseph’s site by Amit Velingkar where he shows you how to change the automatic colour ranges that are used in Excel 2007 for conditional formatting. He even includes some VBA code for this.

jethro's picture

Sundry Excel Tips

I read bunches of excel tips and hints from other websites and every now and again I post links and details of the best ones. Here's a few good ones I have picked up recently.

Counting number of unique entries in a specified range From ExcelTip
Counting number of unique entries in a specified range is simple by using the SUMPRODUCT and COUNT functions.
The range can be all in one column or row, or may span multiple (adjoining) columns/rows.
The formula would be written as: =SUMPRODUCT((1/COUNTIF(A1:C5,A1:C5&""))) where A1:C5 is replaced with the range you want to apply the count to.