If statements two

[Jason]

Awesome, both of those formulas worked great. Thanks! I have one followup
question. I have the following data:

Category Date Amount
A 11/1/01 100.00
A 1/1/00 100.00
B 12/31/04 200.00
C 1/1/00 300.00

As stated before, I have the formula to sum the values in the fields and
divide the ones with invoice date before 1/1/01 by .03 and the ones after
1/1/01 by .029. However, is there a way to incorproate another formula that
will only calculate the ones in category A, then in a sepearte cell calculate
the ones in category B and so on and so fort.

Thanks,

Jason

 

[Jason]

The formula I'm using to do the first set of calculations is:

=SUMPRODUCT(--(B2:B5>=DATEVALUE("1/1/2001")),C2:C5)/0.029+SUMPRODUCT(--(B2:B5<DATEVALUE("1/1/2001")),C2:C5)/0.03

 

[Bob Phillips]

=SUM(IF(A1:A29="A",IF(B1:B29>=--"2001-01-01",C1:C29/0.029,C1:C29/0.03)))

=SUM(IF(A1:A29="B",IF(B1:B29>=--"2001-01-01",C1:C29/0.029,C1:C29/0.03)))

etc.

which are array formulae, they should be committed with Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[driller]

for A's only maybe like this
=SUMPRODUCT(--(B2:B5>=DATEVALUE("1/1/2001")),C2:C5,a2:a5="A")/0.029+SUMPRODUCT(--(B2:B5<DATEVALUE("1/1/2001")),C2:C5,a2:a5="A")/0.03