Hi Bob
You could use SUMPRODUCT for this
=SUMPRODUCT((A1:A3>=E1)*(A1:A3<F1)*(C1:C3)
E1 would contain 01/01/04 and F1 would contain 02/01/04. You could also incorporate the company name into this function
=SUMPRODUCT((A1:A3>=E1)*(A1:A3<F1)*(B1:B3="HTC")*(C1:C3)
Below is a write-up on SUMPRODUCT that you might find useful in using this function
Good Luck
Mark Graesse
(e-mail address removed)
Boston M
*************************************************
SUMPRODUCT
The SUMPRODUCT function will create an array of values for each argument. The corresponding components of each array are then multiplied, and these products are added
The arrays must be of the same size, and in the same direction (vertical or horizontal). However, they do not have to be level. One can use an array of A1:A5 in one argument and an array of B11:B15 in another argument. Arrays of conflicting size will produce a #N/A error
Also, the arrays must be of a definite size. Full column references (A:A) are not valid and will return a #NUM error
The use of a single multiplier is also acceptable. SUMPRODUCT( (A1:A5) * 5
For conditional arguments the return is a 0 if false and a 1 if tru
Non-conditional arguments, values used directly from the spreadsheet, must be numeric. Text arguments will return a #VALUE error
SAMPLE
With the following table in A1:C
A D
A E
A F
B D
B E
B F
C D
C E
C F
And the formula
=SUMPRODUCT( (A1:A9=â€Bâ€) * (B1:B9=â€Eâ€) * (C1:C9)
The resultant arrays produced are
(0,0,0,1,1,1,0,0,0) * (0,1,0,0,1,0,0,1,0,) * (1,2,3,4,5,6,7,8,9
The products of the corresponding components then produce
(0*0*1) + (0*1*2) + (0*0*3) + (1*0*4) + (1*1*5) + … =
(0 + 0 + 0 + 0 + 5 + 0 + 0 + 0 + 0
And the final sum of these products is
*************************************************
----- Bob wrote: ----
Is there a way to use a Date in an Array formula
I'm trying to total by the month and I want January total
Any thoughts appreciated and welcomed
Date Name Amoun
01/02/2004 HTC 22.0
01/05/2004 HTC 25.0
02/10/2004 HTC 41.00
