If Statments

[Jason]

I need to write a formula that sum all the values in an amount field based on
the following criteria:

Line items that have a date greater than or equal to 1/1/2001 need to be
divided by .029.

Line items that have a date less than 1/1/2001 need to be divided by .03.

How would I go about writing this formula? Do I need to use and if statment?

 

[Bob Umlas, Excel MVP]

Assuming the "line items" dates are in col A and the values are in B
=SUMPRODUCT(--(A1:A29>=DATEVALUE("1/1/2001")),B1:B29)/0.029+SUMPRODUCT(--(A1:A29<DATEVALUE("1/1/2001")),B1:B29)/0.03

 

[Gary''s Student]

If the dates are in column A and the items are in column B, then in C1 enter:

=B1/IF(A1<DATE(2001,1,1),0.03,0.029) and copy down

In another cell enter:
=SUM(C:C)