# Excel 97 Array formula problem

seedie 10:19 13 Nov 03
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 03

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

This is NOT an array formula.

seedie 10:53 13 Nov 03

copied and pasted this and returns #value.

Got to go out soon, be back later.

VoG II 11:13 13 Nov 03

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 03

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 03

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 03

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.

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…