Oct 18 2022 07:47 AM
In column J, I am wanting to add the open balance if a date falls before 07/01/2023 and equals the same text in column I (Cost Center). Also, in column K, I am wanting to add the open balance if a date falls after 07/01/2023 and equals the same text in column I (Cost Center). All I can get it to return is 0.00's.
=SUMIFS(F7:F96,B7:B96,"<"&DATE(2023,7,1),E7:E96,"=I6")
Date | QB Inv | Contract | Cost Center | Open Balance | FY23 | FY24 | ||||
American Crystal Sugar-Moorhead | 309100 | $0.00 | $0.00 | |||||||
12/10/2022 | 18439 | 3049 | 359760 | 2,400.00 | 339341 | $0.00 | $0.00 | |||
Total for American Crystal Sugar-Moorhead | $ 2,400.00 | 339351 | $0.00 | $0.00 | ||||||
Anderson Processing | 339360 | $0.00 | $0.00 | |||||||
11/10/2022 | 18446 | 3054 | 359746 | 4,200.00 | 339361 | $0.00 | $0.00 | |||
Total for Anderson Processing | $ 4,200.00 | 359740 | $0.00 | $0.00 | ||||||
Bongard's Creameries | 359742 | $0.00 | $0.00 | |||||||
11/10/2022 | 18454 | 3063 | 359746 | 1,200.00 | 359744 | $0.00 | $0.00 |
Oct 18 2022 07:58 AM
It returns zero because not all conditions apply to at least one row.
Change "=I6" (sic) to I6 ... no quotes, no "=".
Oct 18 2022 08:01 AM
Oct 18 2022 09:15 AM - edited Oct 18 2022 09:17 AM
Please copy-and-paste the formula exactly as it appears in the Formula Bar.
Another problem might be: some data is type text, not numeric. The format of the cell does not matter; and looks can be deceiving. Use formulas of the form =ISNUMBER(F7) to determine if the type is numeric.
The table that you posted is not sufficient for me to help further. First, there are no column and row names. But more importantly, it would help me help you if you attach an example Excel file that demonstrates the problem so that I can see the type of the data, as well as the workbook state (e.g. Automate vs Manual calculation mode).
Click the "browse" link at the bottom of the reply applet. If the forum does not allow that (yet), upload the file to a file-sharing website, and post the download URL. I like box.net/files; others like dropbox.com. You might like onedrive.live.com. But if you use onedrive, IMHO, the download URL should not allow others to edit the file. That will avoid accidental (and purposeful) modifications. I'm all thumbs (sigh). Avoid using drive.google.com. I worry that Google might convert the Excel file to Google Sheets. Not only is that different; but also it might change the context.
If the forum does not allow you to post a URL (yet), spell out part of the URL. For example, the URL for this thread is techcommunity dot microsoft dot com /t5/excel/multiple-criteria-in-sumifs-returning-0-00/td-p/3655757
Oct 18 2022 10:43 AM
Oct 18 2022 02:15 PM
Thanks. Got the file.
The "numbers" in column I are text, not numeric. For example, =ISNUMBER(I6) returns FALSE.
But the numbers in column E are indeed numeric.
Nevertheless, that does not seem to confuse SUMIFS.
(BTW, only half of the "numbers" in column I appear in column E.)
OTOH, the "dates" in column B are text, not numeric Excel dates.
Consequently, the SUMIFS condition "<"&DATE(2023,7,1) fails because DATE returns the integer 45108.
Bottom line: Use ISNUMBER or ISTEXT to ensure that all values are the type that you intended.