Andrew asks:

LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, I have to match a client name, and add all the sales totals:

clientA 10
clientA 10
clientA 10
clientB 5
clientB 5
clientB 5

So if I search for clientA, I need one cell that keeps a running total as sales are added. Lastly, a date range will need to be given, so search for sales from clientA between two dates and keep a running total...

Answer:

Excel formula in cell I3:

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000)))

Explaining excel formula

Step 1 - Find records equal or larger than the first date criterion

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000)))

MATCH(9,99999999999999E+307, B3:B10000) finds last cell in column and returns the relative position (row number). This makes the range expand when new values are added.

INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000)) creates a cell reference to the last cell in column.

B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000)) creates a cell reference to the entire cell range.

(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)

becomes

(B3:INDEX(B3:B10000, 23)>=F3)

becomes

(B3:B25>=F3)

becomes

{40544; 40547; 40550; 40552; 40557; 40558; 40563; 40564; 40567; 40568; 40569; 40573; 40576; 40576; 40579; 40579; 40584; 40589; 40594; 40595; 40598; 40600; 40604}>=40544

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 2 - Find records equal or smaller than the second date criterion

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000)))

(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)

becomes

B3:B25<=G3

becomes

{40544; 40547; 40550; 40552; 40557; 40558; 40563; 40564; 40567; 40568; 40569; 40573; 40576; 40576; 40579; 40579; 40584; 40589; 40594; 40595; 40598; 40600; 40604}>=40574

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Step 3 - Find records equal  to client criterion

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000)))

(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)

becomes

(C3:C25=H3)

becomes

({"Client D"; "Client A"; "Client C"; "Client D"; "Client D"; "Client A"; "Client C"; "Client B"; "Client D"; "Client A"; "Client A"; "Client B"; "Client A"; "Client D"; "Client A"; "Client B"; "Client A"; "Client D"; "Client C"; "Client A"; "Client A"; "Client B"; "Client A"}="Client D")

and returns

{TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}

Step 4 - Multiply array with amounts and calculate sum

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*D3:INDEX(D3:D10000, MATCH(9,99999999999999E+307, B3:B10000)))

becomes

=SUMPRODUCT((B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))>=F3)*(B3:INDEX(B3:B10000, MATCH(9,99999999999999E+307, B3:B10000))<=G3)*(C3:INDEX(C3:C10000, MATCH(9,99999999999999E+307, B3:B10000))=H3)*{550; 380; 130; 130; 660; 180; 200; 380; 810; 590; 770; 830; 290; 220; 880; 380; 140; 150; 180; 920; 890; 830; 520})

becomes

=SUMPRODUCT({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}*{TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}*{550; 380; 130; 130; 660; 180; 200; 380; 810; 590; 770; 830; 290; 220; 880; 380; 140; 150; 180; 920; 890; 830; 520})

becomes

=SUMPRODUCT({1; 0; 0; 1; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}*{550; 380; 130; 130; 660; 180; 200; 380; 810; 590; 770; 830; 290; 220; 880; 380; 140; 150; 180; 920; 890; 830; 520})

becomes

=SUMPRODUCT({550;0;0;130;660;0;0;0;810;0;0;0;0;0;0;0;0;0;0;0;0;0;0})

and returns 2150 in cell I3.

Download excel example file

Running totals within date range.xls
(Excel 97-2003 Workbook *.xls)

Functions:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays