Could someone explain to me SUMIF & dates please

[mapakunk]

What is the formula & the logic of trying to add a range of values (column e) and dates (column a) based on criteria of <=today()-30
in other words the formula would sound like

if column a (date) is less than or equal to today, add cells in column e.

The way I think and the way excel thinks are really different. And I'm having a hard time adjusting. Anyone recommend any websites, books, etc. on being able to think this way.

Sorry if these are dumb questions, but I really want to be able to fiqure this stuff out.

Thank you

[Asala42]

G'day,

What is the formula & the logic of trying to add a range of values (column e) and dates (column a) based on criteria of <=today()-30
in other words the formula would sound like

if column a (date) is less than or equal to today, add cells in column e.

The way I think and the way excel thinks are really different. And I'm having a hard time adjusting. Anyone recommend any websites, books, etc. on being able to think this way.

Sorry if these are dumb questions, but I really want to be able to fiqure this stuff out.

Thank you

SUMIF and COUNTIF start to get an attitude when you apply a criteria more complex than something like "=10". Same with multiple criteria. After scoping out this board I've learned to use Sumproduct for this kind of thing. In your case:

=SUMPRODUCT((A2:A100>TODAY()-30)*(E2:E100))

Basically, the formula takes advantage of TRUE = 1 and FALSE = 0. So you can kind of think the first half of the formula "((A2:A100>TODAY()-30)" as kind of an array of True's and False's (or 0's and 1's) multiplied by the column to be summed.

Hope that helps
Adam

[ This Message was edited by: Asala42 on 2002-05-14 07:37 ]