PDA

View Full Version : Solved: Sumproduct/Array formula for cells with date



Victor
11-24-2009, 04:46 PM
Hi all:

I have this data:

A Date----------##-------Total January-----Total November
1/12009---------10---------14----------------5
11/3/2009-------5
1/4/2009--------4
-----------------3
-----------------7

Arrray enter = (month(A1:A5)=1)*(B1:b5) = 24 and not 14 since a month of a blank cell is 1 (month 1/0/1900).

Any idea how I can solve this situation using arrays.

Thanks for the help.

Victor

Bob Phillips
11-25-2009, 01:41 AM
Try

=SUMPRODUCT(--(A1:A5<>""),--(MONTH(A1:A5)=1),B1:B5)

Victor
11-25-2009, 02:15 AM
xld
Can you explain the use of — before the test, please ?
thanks
Victor

Bob Phillips
11-25-2009, 03:55 AM
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.

Victor
11-25-2009, 11:46 AM
XLT

Nice, "you can coerce to 1/0 with the double unary --" instate of multiply by cero for one condition.

Thanks

Victor