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

## Please login or register to join the discussion.

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.

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.

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.

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

CLINT,

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

It works for me, Richard

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.

Followup on the 9 v 109 option in subtotal

This from the help file:

With kind regards

Clint Westwood

Perfect

Thanks all.

@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.

Just to expand on Stepurhans post.

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

Don't worry

Figured it out. Cheers chaps.

Several 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

Always learning

Not seen that one before and definitely an improvement on my method for such a setup. Thanks for sharing.I understand SUMPRODUCT at last!

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:

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

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.

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.

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

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.

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.

## Please login or register to join the discussion.