Forum Discussion
SUMIFS Problem
Hi,
Given 2 tables, table 1 (T1) in Sheet1 and table 2 (T2) in Sheet2: In T1 column C, I want to sum up numeric data from T2 column C based on two criteria.
1) The text in T2 column B matches the corresponding text in T1 column B
2) The date in T2 column A is before or on the corresponding date in T1 column A
Here are T1 and T2
If I compare the date in T2 column A as equal to the corresponding date in T1 column A, it works.
But when I try using <= or <, I get the following error.
What am I doing wrong? Is the syntax wrong? Is there another way to accomplish this? Do I need to add a date formula?
Any help is appreciated!
Here’s the sample workbook.
Not sure but I believe you should try
"<="&A2
"Why it works?" That a good question. The on-line documentation doesn't mention it but it does mention that you could enter the criteria as a cell reference, a number or a text. The latter must to be used if you want to include <=> signs in a criterion and a cell reference. In your example you could use A2 if you want an exact match of the date in A2. But now you want <= Oct 1, 2019.
You could use "<=10/1/19", but that's not dynamic. Using "<="&A2 will concatenate into "<=10/1/19" and therefor it works.
Thanks, I’ll keep that in mind!
Not sure but I believe you should try
"<="&A2
Perfect! Thanks 🙏
I thought & was concatenation. Why does this work? I'm no expert in excel (•⁔•)
