=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)
The double minus (as in "--") is new to me.
What does it do?

The SUMPRODUCT function works with numbers. It multiplies arrays of numbers
together then returns the sum total of that multiplication. For example:

1...3
2...4
6...2

=SUMPRODUCT(A1:A3,B1:B3) = 23

(1*3)+(2*4)+(6*2) = 23

In this formula:

=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)

We have one array of numbers in the range AR59:AR2000. So, we need to come
up with another array of numbers in order for the SUMPRODUCT function to do
what it does.

This expression will return an array of either TRUE or FALSE:

TEXT(AP59:AP2000,"mmyyyy")=BA58

Based on your posted sample data we get:

BA58 = 012010

12 Dec 2009 = 122009 = 012010 = FALSE
14 Jan 2010 = 012010 = 012010 = TRUE
12 Feb 2010 = 022010 = 012010 = FALSE
12 Mar 2010 = 032010 = 012010 = FALSE
12 Apr 2010 = 042010 = 012010 = FALSE
12 May 2010 = 052010 = 012010 = FALSE
etc
etc

We need to convert those logicals to numbers. One way to do that is to use
the double unary minus --.

--TRUE = 1
--FALSE = 0

--(TEXT(AP59:AP2000,"mmyyyy")=BA58)

Then we get an array of 1s or 0s:

12 Dec 2009 = 122009 = 012010 = --FALSE = 0
14 Jan 2010 = 012010 = 012010 = --TRUE = 1
12 Feb 2010 = 022010 = 012010 = --FALSE = 0
12 Mar 2010 = 032010 = 012010 = --FALSE = 0
12 Apr 2010 = 042010 = 012010 = --FALSE = 0
12 May 2010 = 052010 = 012010 = --FALSE = 0
etc
etc

Now we have 2 arrays of numbers, the array of 1s and 0s and the array of
numbers in the range AR59:AR2000. Those arrays might look something like
this:

0...10
1...14
0...0
0...7
0...22
0...19
etc
etc

The arrays are multiplied together:

0*10 = 0
1*14 = 14
0*0 = 0
0*7 = 0
0*22 = 0
0*19 = 0
etc
etc

SUMPRODUCT then sums the results of that multiplication:

SUMPRODUCT({0;14;0;0;0;0}) = 14

So, lookup "012010" in the range of dates AP59:AP2000 and return the
corresponding numeric value from AR59:AR2000:

=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)

=14

For more on SUMPRODUCT see this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"GBExcel via OfficeKB.com" <u55438@uwe wrote in message
news:a275058b31ae0@uwe...
Thank you. Will try it.

The double minus (as in "--") is new to me. What does it do?

GBExcel

T. Valko wrote:
Try this...

=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)

Will the date in cell BA58 appear *only once* in the range AP59:AP2000?

Yes, that is correct.

GBExcel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1