OR Condition with SUMIFS()

Introduction

When I was creating an Ageing Schedule for my Debtors (Receivables), I created a summary at the bottom in which I wanted to summarise the various conditions in the table such as

What is the value of all invoices 

• Paid
• Current
• Overdue

I used the SUMIF() and  SUMIFS() functions for this but SUMIFS() can only tell me AND conditions and not OR conditions ... read on!

Ageing Schedule

Here is the example ageing schedule I was working on:

Here is the summary I created at the bottom of that schedule:

And that is what I wanted but notice the formulas I had to use

Total Paid =SUMIF(Table2[Status],"Paid",Table2[Amount])

Total Outstanding =SUMIFS(Table2[Amount],Table2[Status],"<>Paid")

Total =SUM(E31:E32)

Those formulas gave me the right answers, with nothing to complain about except that I had to use "<>Paid" to find the Outstanding balance because SUMIFS() can only tell me AND and not OR

By the way, I used the SUMIF() function to find the Paid value because I was demonstrating this problem to my class and wanted them to see both SUMIF() and SUMIFS() in action.

Combine SUM() and SUMIFS()

I had not considered the AND/OR context of SUMIFS() before but after a little bit of research, I found the answer to be quite straightforward, like this:

Total Outstanding =SUM(SUMIFS(Table2[Amount],Table2[Status],{"Current","Overdue"}))

All we do is we wrap a SUM() function around SUMIFS() and for our criteria, we use Array Constants of the names I used to signify outstanding: that's the {"Current","Overdue"} part of the formula.

That's it, that is how to create an OR condition using the SUMIFS() function

Database Functions

I know a lot of people either don't like database functions or they have never heard of them but I also demonstrated their use in this example, as follows:

These functions work like a charm and they cope with AND/OR with consummate ease!

By the way, I had a thought as I was putting these DSUM() functions together: rather than setting up the helper tables that you see in C36:D38 and C40:D41, can we use the database itself for this?

Well, look at the formula in cell E42 which shows the answer to that is Yes, we can! ... 

I always say that the Database functions are so clean and tidy when compared to SUMIFS() and SUMPRODUCT() but when allied to an Excel table, that doesn't look to be true, does it? Try this, though:

=DSUM(A13:F28,E13,E13:F28)

There, that's tidy!

Conclusions

That's another arrow to your both: wrapping SUM() round the SUMIFS() function to find OR conditions rather than just AND conditions. You also saw from me, again, how simple and valuable and versatile the Database functions can be.

Duncan Williamson

12 October 2022