You want to only sum values whose dates fall in between A4 and A3, right?
So, another way of checking to see if a "date" is between the dates in A4 and A3 is like this:
And the format of the SUMIFS function is:
=SUMIFS(range to sum,criteria range 1, criteria 1, criteria range 2, criteria 2,...)
So, what if we had a few criteria, one of which is we wanted to only sum records if the name column was "Bob", it might looks something like this:
=SUMIFS(Z1:Z100,A1:A100,"Bob",...)
Note that by using that criteria without an operand like equals, greater than, less than, etc, it is assumed to be equals.
So the formula above would only sum records where column A is equal to "Bob" (not "less than" Bob, or "greater than" Bob).
The way you wrote your formula, without any operands, it is assuming you mean equal.
So your original criteria was checking for the following:
-1st Criteria: date in column A equal to A3
-2nd Criteria: date in column A equal to A4
Since ALL criteria must be met, that means it would only sum records where your date in column and A3 and A4 are all the same.
If your dates in A3 and A4 are not the same, that will never happen, so you will always get zero.
(You also had the issue of using the Text function, which returns Text, and comparing Text to Dates/Numbers does not work, but I see your figured that out with your second formula).
So, you need to use the greater than/less than formulas to get your date within a date range.
If you take a few moments to read through the explanation in the link provided that you used to come up with your formula, that should help explain (or at least compliment) what I said here.
Does that make sense now?
