How can I use a cell reference in evaluation criteria?

  • Thread starter Thread starter dannycombs
  • Start date Start date
D

dannycombs

I'm trying to evaluate a date, contained in cell C1, against an array o
dates, contained in column A. Specifically, I'm using SUMIF to add u
dollar figures in column B when there corresponding date, in column
is early than the reference date in C1.
SUMIF(A1:A5,"<=C1",B1:B5)

If I explicity state the date in the formula such as
"<=4/26/2005"
but I need to compare it to the date contained in the reference cell s
I can easily change it. When I try the former it doesn't work. Th
resultant sum is zero as if no dates in the column A array met th
criteria, even though they do.

Can anybody tell me how to make this work?

Thanks,
Dann
 
Thanks much. Obvious enough I guess I should have thought of it.

Now I have a second problem. What if I want to evaluate the date
against two criteria such that the SUMIF will find it true if it's
earlier than the date in C1 but later than the date in C2? Can I
combine two or more evaluation criteria in one criteria block?
Thanks,
Danny
 
It gets (for lack of better term) messy when evaluating 2
or more criteria and attempting to use SUMIF. For example,
to sum for all dates between C1 and C2, you could use:

=SUM(B1:B5)-SUMIF(A1:A5,"<"&C1,B1:B5)-SUMIF
(A1:A5,">"&C2,B1:B5)

But using an array or SUMPRODUCT is much easier, esp. when
adding or removing criteria. The formula below will
produce the same results as above:

=SUMPRODUCT((A1:A5>=C1)*(A1:A5<=C2)*B1:B5)

HTH
Jason
Atlanta, GA
 
Awesome thanks. I'm not sure I understand this particular permutation
of SUMPRODUCT, though I understand the formula generically, but it does
work exactly as desired and as you suggest so thanks.

To clarify, the * works like an AND and if it were a + it would work
like an OR, is that correct? Plus, non-numerical array values take a
value of zero?

Danny
 
Jason Morin said:
It gets (for lack of better term) messy when evaluating 2
or more criteria and attempting to use SUMIF. For example,
to sum for all dates between C1 and C2, you could use:

=SUM(B1:B5)-SUMIF(A1:A5,"<"&C1,B1:B5)
-SUMIF(A1:A5,">"&C2,B1:B5)
Click to expand...
....

Why not just

=SUMIF(A1:A5,">="&C1,B1:B5)-SUMIF(A1:A5,">"&C2,B1:B5)
 
Back
Top