Array Formula

[Shutterbugg_56]

I am trying to create an array fromula to sum one column based o
criteria from two other columns. I can get the formula to work as lon
as my second criteria is not based on the date.



=SUM(IF((D214="General")*(F2:F14<"2/23/2004"),G2:G14))

If the two criteria are based on text they seem to work, is it eve
possible to make the date a criteria and if so am I inputting into th
formula wron

 

[Dave R.]

You don't need the IF in there. The reason you are multiplying expressions
(with *) is because the value 1 will be returned if the first statement is
true (d2=general), and that will be multiplied by 1 or 0 depending on
whether the 2nd, 3rd, etc. expression is true or false.


so try something like (unchecked):

SUM((D214="General")*(F2:F14<datevalue),G2:G14))
and enter with control shift enter.

 

[Dave R.]

Sorry shutterbug -- that was a poor solution, probably wrong.

You can use sumproduct for this:
=SUMPRODUCT((D23="general")*(F2:F3>(DATEVALUE("2/12/2004"))),G2:G3)

just change the date and cells to match your stuff. You can enter this by
pressing enter, no need to enter as an array formula.