Excel 97 Array formula problem

  seedie 10:19 13 Nov 2003

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.


C50 contains the reference date.



  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


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


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

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

Best Android emulators for Mac

TV & Streaming : comment regarder Roland Garros ?