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.
Recent Posts
See AllRevisited starter script from January 2021: Split Excel file into separate files Excel is essential, and Python is the future - forcing ourselves to practice the latter by automating some of the commo