# Useful Excel Functions

#### MarkAndrews

##### Well-known Member
I'm not too sure if there's been a post like this before, but here goes...

Have you ever found a function (or anything else) in Excel that has surprised you? or anything that could have made your life easier prior to finding it

Reason I ask, recently (through this board) I discovered the Indirect function, using data validation

May be useful to people if brief scenario's are included in any reply's (just a thought)

Mark

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
INDIRECT would deffo be up there as a formula in its own right, as well as within data validation (usually combined with SUBSTITUTE). SUMPRODUCT is a fave as well. I use those 2 on a daily basis.
On the rare occasions I use pivot tables, I use GETPIVOTDATA(despite it's horrific user interface and syntax).

INDIRECT would deffo be up there as a formula in its own right, as well as within data validation (usually combined with SUBSTITUTE). SUMPRODUCT is a fave as well. I use those 2 on a daily basis.
On the rare occasions I use pivot tables, I use GETPIVOTDATA(despite it's horrific user interface and syntax).

I've never really understood SUMPRODUCT

**note to self - must learn**

I've never really understood SUMPRODUCT

It's a very simple formula to understand. There are other board members who could probably explain it much better, but think of it as a COUNTIF or SUMIF formula that can handle multiple conditions...

COUNTIF:
Code:
``=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),--(Range3=Criteria3))``

SUMIF:
Code:
``=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),--(Range3=Criteria3), (Amount_to_be_summed))``

Thanks Neil, will have a play with it in a draft document

Mark

How come this was moved? I put it in Lounge to chat about...

I strongly agree with SUMPRODUCT - I thought I was the bees' knees knowing how to do simple array formulae, but Sumproduct removes the need for CSE formulas in a lot of cases.

Richard

Yep, SUMPRODUCT gets a vote from me too.

I also like INDIRECT and OFFSET, but for sheer grunt when you analyse and report on large amounts of data, Pivot tables are my favourite. Their main drawback it that coding pivot tables in VBA can be messy, but I'm getting there.

Denis

I like OFFSET too - it's only when you start doing some more complicated stuff that you actually begin to appreciate how useful it can be (true of INDIRECT also). Of the newer functions that have got me excited (maybe the wrong word here? - Oh well, maybe I am a little geeky ) is SQL.REQUEST.

Richard

Understand the difference between binary and sequential search.

Careful data layout taking advantage of binary syntax will speed you up.

Replies
2
Views
990
Replies
5
Views
1K
Replies
0
Views
1K
Replies
1
Views
5K
Replies
11
Views
807

1,221,008
Messages
6,157,357
Members
451,416
Latest member
Ilu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back