0
812

# SUMIF

SUMIF

• ### HMRC Debit & Credit Card Transactions Check

My bookkeeper excel whizz has left me unattended!

I have a spreadsheet where I can't get the SUMIF function to work.

Column A = Description
Column B = Cost Centre
Column C = Expense Code
Column D = Debit Value
Column E = Credit Value

Column B and C contain a mixture of different coding.

I want to do a rec and add up the debit and credit values for specific expense codes (column C)and return the debit value. Repeated for the credit values and different expense codes.

Can someone help me with the formula please?

### Replies

01st Mar 2016 12:23

If I understand correctly

and, assuming you have a cell say "F1" where you are entering the code you are interested in, then

=SUMIF(\$C:\$C,F1,\$D:\$D) and =SUMIF(\$C:\$C,F1,\$E:\$E)

would do it. You may wish to adjust the column references.

Thanks (1)
01st Mar 2016 12:26

Formula

=SUMIF(C4:C19,"D/D",D4:D19)

Where

C4:C19 is the range of the expense code you're checkingD/D is the name of the individual expense code you are wanting totals for (assuming it is text, it does need to be in quotes as shown here)D4:D19 is the matching range of debits you want to add up. It is vital that this aligns exactly with the original range (i.e. both go from row 4 to row 19 in this example)

If you want a single total for a bunch of expenses codes just repeat the formula for each code with pluses in-between.

Thanks (1)
01st Mar 2016 12:33

If

You list the valid expense codes underneath, (or elsewhere) you can reference them as the middle part of the function and get the Dr and Cr total for each one as a list alongside.  Always good to add it up at the bottom and take away from the original total to make sure nothing is missed.

Thanks (2)
01st Mar 2016 12:38

Most appropriate method may depend on what you want to do with the result returned.  If it is the source cell for other areas of the sheet (ie has "dependents") then use SUMIF or SUMPRODUCT or similar function.  If you just want to view the affected items and totals, then you *might* be better off by auto-filtering the table.  Then above the Debit column enter =SUBTOTAL(9,D4:D19), and above the Credit column enter =SUBTOTAL(9,E4:E19).

Then just autofilter the table by the desired expense code, and the relevant totals will be shown above the column headings, and the individual entries making up those totals will be shown in the table with others excluded.

Again, if it is just totals that interest you, then a PivotTable may provide all of the expense code totals at a glance, without faffing about with SUMIF

With kind regards

Clint Westwood

Thanks (1)
01st Mar 2016 12:52

CLINT,

nogammonsinanundoubledgame wrote:

Most appropriate method may depend on what you want to do with the result returned.  If it is the source cell for other areas of the sheet (ie has "dependents") then use SUMIF or SUMPRODUCT or similar function.  If you just want to view the affected items and totals, then you *might* be better off by auto-filtering the table.  Then above the Debit column enter =SUBTOTAL(9,D4:D19), and above the Credit column enter =SUBTOTAL(9,E4:E19).

Then just autofilter the table by the desired expense code, and the relevant totals will be shown above the column headings, and the individual entries making up those totals will be shown in the table with others excluded.

Again, if it is just totals that interest you, then a PivotTable may provide all of the expense code totals at a glance, without faffing about with SUMIF

With kind regards

Clint Westwood

Shouldn't the '9' be '109' Clint?  9 will return the grand total even if the list is filtered.

Thanks (0)
01st Mar 2016 14:33

It works for me, Richard

Richard Willis wrote:

Shouldn't the '9' be '109' Clint?  9 will return the grand total even if the list is filtered.

I have rather forgotten the distinction between "9" and "109".  I have always used 9 and it has always worked for me.

The 109 option first came in I think with the 2007 version of Excel (and onset of xlsx file format).  9 option is available across all versions. Obviously there must be a difference in behaviour, but as I say, only ever used 9 so never really looked further.

With kind regards

Clint Westwood.

Thanks (0)
02nd Mar 2016 05:38

Followup on the 9 v 109 option in subtotal

nogammonsinanundoubledgame wrote:

Richard Willis wrote:

Shouldn't the '9' be '109' Clint?  9 will return the grand total even if the list is filtered.

I have rather forgotten the distinction between "9" and "109".  I have always used 9 and it has always worked for me.

The 109 option first came in I think with the 2007 version of Excel (and onset of xlsx file format).  9 option is available across all versions. Obviously there must be a difference in behaviour, but as I say, only ever used 9 so never really looked further.

With kind regards

Clint Westwood.

This from the help file:

Quote:
Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded

With kind regards

Clint Westwood

Thanks (0)
01st Mar 2016 12:39

Perfect

Thanks all.

Thanks (0)
01st Mar 2016 12:43

@Clint

I started writing a second post along the lines of yours and then decided not to post it. I'm so glad I did - yours is much better.

Thanks (0)
01st Mar 2016 12:47

Just to expand on Stepurhans post.

How would I get it to add up two expense codes?

Thanks (0)
01st Mar 2016 12:54

Don't worry

cheekychappy wrote:
Just to expand on Stepurhans post. How would I get it to add up two expense codes?

Figured it out. Cheers chaps.

Thanks (0)
01st Mar 2016 14:51

Several expense codes

cheekychappy wrote:
Just to expand on Stepurhans post. How would I get it to add up two expense codes?
If your expense codes are numerical values in an ordered range, and you wanted to look up the debit total for a range of codes, say between 1000 and 4000, then something like

=SUMPRODUCT(D4:D19*(C4:C19>1000)*(C4:C19<4000))

With kind regards

Clint Westwood

Thanks (2)
01st Mar 2016 14:54

Always learning

nogammonsinanundoubledgame wrote:
If your expense codes are numerical values in an ordered range, and you wanted to look up the debit total for a range of codes, say between 1000 and 4000, then something like

=SUMPRODUCT(D4:D19*(C4:C19>1000)*(C4:C19<4000))

Not seen that one before and definitely an improvement on my method for such a setup. Thanks for sharing.
Thanks (1)
01st Mar 2016 17:13

I understand SUMPRODUCT at last!

nogammonsinanundoubledgame wrote:

If your expense codes are numerical values in an ordered range, and you wanted to look up the debit total for a range of codes, say between 1000 and 4000, then something like

=SUMPRODUCT(D4:D19*(C4:C19>1000)*(C4:C19<4000))

With kind regards

Clint Westwood

Thanks for sharing this Clint. I've seen the SUMPRODUCT function used before for summing arrays, but it's one function that I've never got my head around, until now.

If it's of use to anybody else, I did a bit of research off the back of this post to understand why that works. The following extract I found particularly useful:

Quote:

The SUMPRODUCT function works with arrays, but it doesn't require the normal array syntax (Ctrl + Shift + Enter) to enter. The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array.

https://exceljet.net/excel-functions/excel-sumproduct-function

Thanks (0)
01st Mar 2016 12:54

Two together

=SUMIF(C4:C19,"First code",D4:D19)+SUMIF(C4:C19,"Second code",D4:D19)

Repeat for as many codes as you wanted added together.

As others have said, you could simply list the codes and have the formula reference them (so if you have a code in cell F1, you'd replace the middle section with just F1 without quotes)

As others have also indicated, absolute referencing is probably wise so you can copy the formula into multiple cells. If you just have "C4:C19" then those letters and numbers will shift if you copy the formula to another cell. If you use "\$C\$4:\$C\$19" then the formula will always use that specific range no matter where it is copied to.

Thanks (1)
02nd Mar 2016 07:51

And for reference

Grouped rows/columns count as manually hidden (i.e. "9" would continue to add them in, "109" would exclude them).

Personally I would always use "109" as it gives a WYSIWYG result. If you have a mixture of filtered and grouped rows, although the nightmare scenario giving rise to that is a bit difficult to comprehend :-) , then you can compleetly lose track of what the result means.

Thanks (0)
02nd Mar 2016 08:02

One reason why you might use 9 rather than 109 is if you intend to share the workbook with others who may be on an older version of Excel (ie 2003) and wish to preserve backward compatibility for those users.

With kind regards

Clint Westwood

Thanks (0)
02nd Mar 2016 08:57

SUBTOTAL 109

does work in Excel 2003. I think that was the first version it ws introduced in (I stand to be corrected). But it would indeed be a problem for earlier versions.

Thanks (0)
03rd Mar 2016 12:49

On a lateral theme

I only very recently learned how to copy and paste a filtered list!  For the benefit of anyone else who has not worked it out, if you go to 'Find and Select' icon and click the dropdown there is a choice called 'Go to special'.

Under that there is a choice 'Visible cells only' which enables one to copy the filtered list.

If anyone knows an easier way I would be glad to know.

Thanks (0)