7 January 2013

First client

31 December 2019

Last software update

< 2 hours

Support response

StatementReader is used by forensic accountants, bookkeepers, insolvency investigators, solicitors, auditors and business review professionals to stop wasting time and talent converting bank statements into Excel

This website uses cookies to enhance the user experience.  Click here for our cookie statement and here for our privacy policy.

All Rights Reserved 2012-2020 Suave Software Limited. London, England, United Kingdom. Email Us

  • LinkedIn Social Icon
  • Twitter Social Icon

Multi-condition summary using SUMPRODUCT


SUMPRODUCT is a powerful formula that can be used to summarise transactions that satisfy many different conditions. Here are some examples:

- Show the total credits where the date is after 1 January 2013 and before 31 December 2013

- Show the total credits where the balance was overdrawn but within the agreed overdraft limit

- Show the total credits where the description includes the word 'HMRC' or 'VAT'

SUMPRODUCT takes each element within the formula and multiplies each row, adding together the results. For example, the following formula will multiply A1 and B1, A2 and B2, A3 and B3, and display the total sum.

=SUMPRODUCT ( A1:A3 , B1:B3 )

To use this with conditions, use the following structure:

=SUMPRODUCT ( --((A1:A3)>2) , B1:B3 )

The above example has provided a total of B1, B2 and B3 where the respective value in column A is greater than 2.

Expanding this further, to display a total of B1, B2 and B3 where the respective value in column A is greater than 2 and less than 10.

=SUMPRODUCT ( --((A1:A3)>2) , --((A1:A3)<10) , B1:B3 )

Dates can be inserted by referencing a date cell, or by using the DATE formula. Words can be found by using the ISNUMBER and FIND formulae.

#Excel #Formula #All