Hi Bob
One error in my formula. The range sizes must be identical
=SUMPRODUCT((B1:B10000=>yourdate)*(J1:J10000="M")
Also, I was digging around on the web for some help information on SUMPRODUCT. Most of what I found was for specific applications. Here is a short (and incomplete) write-up that I have put together
*************************************************
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
*************************************************
----- Mark Graesser wrote: ----
Hi Bob
It looks like you are trying to do a COUNTIF with two conditions. The best way to do this would be with the SUMPRODUCT function
=SUMPRODUCT((B1:B1000=>yourdate)*(J1:J10000="M")
You have to use a specific range with the SUMPRODUCT Function. You can't use B:B or J:J. Also this is not array entered
Good Luck
Mark Graesse
(e-mail address removed)
Boston M
----- Bob wrote: ----
I have a Date in column B,gender in column J
formula of If(ColB:B =>a date,Countif("J:J:","M"),""
entered as an array formula,but it doesn't work.Trying to
total after a given date. Any HELP appreciated
