Excel Sum Formula Returns 0 with Nested Sumifs
I Have a Table with Transactions: Invoice | Date | Desc | Amount | Acct1 | Acct2 11112 | 1/1/2020 | Test A1 | 100.00 | 1001 | 4001 11113 | 1/1/2020 | Test A2 |...
I have a table with transactions:
invoice | date | desc | amount | acct1 | acct2
11112 | 1/1/2020 | test a1 | 100.00 | 1001 | 4001
11113 | 1/1/2020 | test a2 | -50.00 | 1001 | 5001
11114 | 2/1/2020 | test b1 | 200.00 | 1001 | 4001
I'm trying to construct a formula that will total based on date and an account. So on a different sheet I have the following grid below. The top line is row 10.
[A]|[B]|[C]|[D]|[E]|[F] |[G]
xxxxxx | 1/1/2020 | 2/1/2020
1000 | |
1001 | [f12] | [g12]
1100 | |
2001 | |
4001 | |
5001 | |
Cells "[f12]" and "[g12]" should have the same formula, which I have here:
=ROUND(SUM(SUMIFS(TRANSACT[AMOUNT],TRANSACT[ACCT1],ACCOUNTS!$A12,TRANSACT[DATE], ">="&ACCOUNTS!F$10,TRANSACT[DATE],"<"&ACCOUNTS!G$10),-SUMIFS(TRANSACT[AMOUNT],TRANSACT[ACCT2],ACCOUNTS!$A12,TRANSACT[DATE], ">="&ACCOUNTS!F$10,TRANSACT[DATE],"<"&ACCOUNTS!G$10)),2)
The correct answer for cell [f12] should be "200.00", but the formula returns "0". When I break out, or "un-nest", the formula, the first SUMIF returns "200.00", and the second SUMIFS returns "0".
But when I go to SUM it, I get "0". In fact, if I SUM the 2 cells, I get "0". The craziest thing to me is that when I hit "Insert Formula" to review the formulas, it returns the correct amount, 200.00! I attached an image.
I can't wait for someone to point out an obvious error that I am just missing.
EDIT: I have added another picture for clarity. My problem is that the formula appears to work in pieces, but when I go to SUM it, it doesn't add up correctly. Please advise.
2 Answers
I see three (3) problems with the provided formula, after setting it up here:
- First, the
Tablecell addressing in the formula:AMOUNT,ACCT1, andACCT2do not match the headers you have in the provided Table. Make appropriate changes to fix... - Second, your
cells in row 10references which very obviously must intend to look to the headers of your input table... well, I'm pretty sure you yourself don't have an issue here since it turns out you cannot, by[e12]and[f12]mean what most anyone else would have thought you meant (cellsE12andF12), so row 10 is probably right, but if it is row 11, as one would at first suspect from the [e12] amd [f12] references, they need fixed. - You need something for
ACCOUNTS!F$10to refer to. Or you also get $0. (Bear in mind I'm still not sure just where your output table is... that cell just mentioned seems to be one cell past the2/1/2020header in your output table so whatever address really applies.
Fix the two for sure issues (numbers 1 and 3), and number 2 also if that really is an issue, and you're golden: it gives ME the correct $200 result. For both [e12] and [f12].
Given your input table is a Table, I wonder if you've considered your output table as a Table. If so, especially if you actually have done so, not just considered it, why not replace those ACCOUNT!cell addresses (the "C$11" type, not the "$C11" type) with the Table header references? If it's just ad hoc work, that's a clearly good reason to not bother, of course, but if permanent, why not, eh?
I do not have my work from yesterday but the cell I refer to in the answer could be better described as the cell in the header row one column to the right of the column for "[f12]" so it would be cell G10 in your updated layout.
The moment I put the date "3/1/20" into cell G10, the formula gives the correct result.
This is because you are comparing, for one of your SUMIFS(), the date in header cell F10 to the date in cell G10. But with G10 empty, it has a value of 0 and so the formula fails to give the correct result for that portion and that makes the whole thing fail. For that matter, the formula REQUIRES G10 to be a later date, even if by only a day (actually, if entered so it had a "time" component, not just a date, even later in the day on 2/1/20 would work, but it MUST be filled in and it MUST be later).
You can solve that many ways: always have that next column headed with the next appropriate date; have it be some date a hundred years in the future if you like; create a Named Range to refer to instead and give it a date like one a hundred years from now, or even a formula that looks to the rightmost header, treats it as a date, and adds 1 to it; edit in an addition so it reads (&ACCOUNTS!F$10+1)) (VERY important to include that extra set of parentheses!) so the value in the formula is ALWAYS one day later than the heading of the last column in use, but you don't have to have a weird-looking column header with nothing in the column underneath it.
And PLENTY of other ideas. I like the last best because it takes no "maintenance" if you extend the table rightwards... the formula itself carries the solution.
By the way, a common issue in situations like this, but NOT in this case, is when the values be in some way looked up, as a SUMIFS() sort of does, are text in the source for what to be looked up, but not in the place the looking up is done, or the opposite. Fortunately, Excel sees a subtle difference in that if the "looking up" essence were involving something that COULD be a string instead of just numbers, Excel enforces the formats and often fails to find any match. But if the function doing the "looking up" is one expected to be doing arithmetic, it treats anything that looks like a number as a number regardless of formats. Again, not a concern here, but that's precisely why I mention it: a person could spend hours trying to figure it out thinking it's a format mismatch but that isn't solving it but it has to be it but it isn't solving it... cycling on station and not even on the right path to a solution. So that subtle difference can make a difference to troubleshooting. Not here though!
(Sorry about the extra answer vs. a reply comment. I cannot place comments.)
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
