Excel 97 Array formula problem

seedie 10:19 13 Nov 2003
Locked

Personal expenditure sheet.

Col A has transaction type eg D.D. direct debit,
Switch, ATM etc.

Col B has payee eg J.Sains, Argos, etc

Col C has the date of transaction.

Col D has the Value.

I want sum transaction values of a particular type, eg ATM on or after a given date. this formula returns 0.00 and I'm a bit stuck.

{=SUM(IF(AND(A64:A102="ATM",C64:C102>=C50),D64:D102))}

C50 contains the reference date.

Thanks

CD

VoG II 10:32 13 Nov 2003

=SUMPRODUCT((A64:A102="ATM") * (C64:C102>=C50)*(D64:D102))

This is NOT an array formula.

seedie 10:53 13 Nov 2003

copied and pasted this and returns #value.

Got to go out soon, be back later.

VoG II 11:13 13 Nov 2003

I've done a mock-up using just rows 1 to 10, with the "after" date in C13 and the following returns the correct result:

=SUMPRODUCT((A1:A10="ATM") * (C1:C10>=C13)*(D1:D10))

seedie 12:58 13 Nov 2003

No fillings so things are looking up.

Had a little play with your formula a few minutes ago and found that this returned 2 which is the number of times I went to ATM on or after 08/01/03

=SUMPRODUCT((A64:A102="ATM")*(C64:C102>=C50))

The #value! error occurs when the formula asks to sum the value in Col D of the two occurances in A and C.

As you say, it works for you so must be my end.

The first two conditions must be TRUE to evalute the third ?

VoG II 13:12 13 Nov 2003

What are the values in Column D?

I've used numbers ("as is") and also formatted as Currency. These both work.

If you have entered the values as text (e.g. £1.99) the formula won't work.

seedie 13:12 13 Nov 2003

Just done a 10 row mock up myself and it worked. I'll have a closer look at working sheet; thanks for your help and direction

CD

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?