SUMIF puzzler with ranges

Spreadsheets beware!
This all goes to show how easy it is to 'knock up a formula' which appears to do the job, which then sits somewhere inside a more complicated set of calculations ... which then fail mysteriously, or worse still, give the wrong answer without anyone knowing.

In the world of software development, tests against expected results should be created, to verify correct operation of subcalculations with various critical values. However, Excel and spreadsheets in general do not assist with the addition of these 'regression tests' to the worksheets.

Chris/Clint
Chris: It doesn't suit normal acs package that I could find because the customers are Teams/Clubs who have their own members and the amount they pay to me depends to some extent on the number of members. Therefore the debtors list and statements needs to quote total members for each Team/Club.

Clint: Thanks for that I will give it a try.

Paul

no help here
anon, I cannot get sumif to work across sheets. Perhaps someone else can. I tend not to try too hard when that happens and just fall back on the array formula solution.

Paul, it looks to me that you want to look into "advanced filters" for the solution. Difficult to say from available info.

Paul
Having slept on it I think perhaps that some of your problems may stem from having invoices and receipts listed in separate worksheets. There may be other reasons that require that, and I am not saying that your particular problem is insurmountable, but it certainly makes it a lot easier if you have just one worksheet of data. To take a simple example, suppose:

Column A = Client identifier/ref/name
Column B = Transaction type (invoice/cash)
Column C = Transaction reference
Column D = Transaction date
Column E = Transaction amount (Positive for invoice or payment, negative for credit note or receipt)
Column F = Cumulative balance

Instead of Column F you could just display the visible subtotal above column E using =SUBTOTAL(9,E:E) or similar function (actually if you put that function verbatim in column E you would get a circular reference - have to exclude that cell)

Then autofilter the database and filter on client ref to get both a detailed statement for that client and a total balance owing.

Personally I prefer having a cumulative balance (ie column F), and I have a neat solution for this if you are interested. It is not necessarily obvious (cue for all the others to pile in with it!)

This simplified version (that includes a cumulative balance column) assumes that remittances settle invoices in date order, which is not necessarily the commercial reality, ie if invoices are in dispute.

Odd, that.
I can reproduce your problem. It seems that the sum_range must be the same dimensions as the comparison range using Sumif. There may be a solution using SUMIF but my solution is:
For convenience, name the data in column A as "colA", column B as "colB" and so on, and say that the test value is contained in a cell named "refCell".

Then your sum would be returned by the array formula:

=SUM(IF((colA=refCell)+(colB=refCell),1,0)*colC)

The array formula is entered by holding down the control+shift keys when hitting the enter key. In the formula bar the formula should be displayed surrounded by curly brackets, ie:
{=SUM(IF((colA=refCell)+(colB=refCell),1,0)*colC)}
You do not manually enter the curly brackets.

Clever!
I can't quite see how you'd use it, but this seems like quite a clever thing.

You've told your SUMIF formula to look at the first two columns, and it appears to be associating the third and fourth columns. ie column 1 associates with column 3 and column 2 associates with column 4. If you change you criteria to "x", the result is 100.

What are you trying to achieve?

Hope this helps your understanding Barry:
For an overview of array formulas see

http://www.cpearson.com/excel/array.htm

The expression (variable_A=variable_B)
returns a boolean value either TRUE or FALSE.
If you use a boolean value in an arithmetic calculation then it treats TRUE = 1 and FALSE = 0.
If you apply a boolean test (IF statement) on a numerical value then it will return FALSE if the tested expression evaluates to 0, else TRUE.

Applying this to the solution given, suppose that your data starts at row 1 then consider the formula

=IF((A1=refCell)+(B1=refCell)),1,0)*C1
what you are doing is multiplying C1 with either 1 or 0 depending on whether
(A1=refCell)+(B1=refCell) evaluates to TRUE or FALSE. If (A1 = refCell) then the numerical equivalent of the first half is 1, and the same applies to the second half if (B1=refCell). Add the two together and you get a non-zero result if either half evaluates to TRUE. A non-zero result returns TRUE when subjected to the boolean test.

All that the array formula does is replicate the above for each row in the array and SUM the lot.

SUMIF/DSUM
I use DSUM for managing a small debtors list that would not suit a normal acs package.

I have a spreadsheet with a list of sales and another with a list of receipts. The DSUM will calculate total sales to a particular entity and total receipts thereby giving a debtors list.

Is there any formula I can use that will bring in a list of transactions from different sheets thereby creating a detailed statement?