What is wrong here? =SUMIFS(D2:D3,">(TODAY()-14)",(N2:N3))

Your parameters are in the wrong order. The last parameter should be a criterion condition, according to microsoft Office SUMIF documentation. So the N column part should be first.

From the link:
Important: The order of arguments differ between the SUMIFS and SUMIF functions. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order.

Thanks for your response. I did read that page but I still don’t understand. I tried this and it still does not work, although it seems like it should address the problem:

=SUMIF(D2:D3,”>(TODAY()-14)”,N2:N3)

Specifically, it comes up with $0.00, even if I reverse the >. It seems like it should in either the > case or the < case show a value (Note: there are values in the cells being referred to).

The syntax looks right to me now.
What does TODAY()-14 evaluate to?
Does D2>(TODAY()-14) evaluate to TRUE or FALSE, or to some other value?

I don’t know the SUMIF or SUMIFS functions, and I never use them.

You can accomplish what you propose with SUMPRODUCT, as:

=SUMPRODUCT(( A1:A10) * (B1:B10 > TODAY() – 14))

I used different ranges from yours, but this will work if you substitute your (equally-sized) ranges instead. With the SUMPRODUCT function, both ranges have to be similarly dimensioned, which means ten rows in this case.

I’ll play with SUMIF / SUMIFS and revisit if I come up with a solution. I never much liked those, though.

Here is how it can work:

Let’s say that I have a range of values in A1:A10.
Assume a range of dates in B1:B10.
In C1 enter the formula that you want to evaluate to check the dates* and make sure that the return value is “a number”, not “a date”. Use that return value in the function.

So the formula that I’m using in C1 is:
= today() – 14

This means that I have C1 shows the value 41523 (which represents today’s date value – 14), and which I can use in the function.

Now the formula “works”, and I hate it:
=SUMIFS( A1:A10, B1:B10, ”> 41523”)

I hate it because the “41523” value is hard-written into the formula instead of enabling a relative formula (entering ”> C1” instead of 41523 causes a failure, and because it’s so much more of a pain to write than my beautiful SUMPRODUCT function, which I’ve been using with great success for years.

Rex, sorry for the delay: it does evaluate true/false. today()-14 on its own evaluates to the date -14 days.

I appreciate the help, CWOTUS, but I was not able to make that work. I even copied and pasted it directly in without changes, and it immediately returns an error.

What you said bothered me, @erniefernandez, because that SUMPRODUCT function is SO helpful to me. So I tried it myself, and you’re absolutely right, the cut-and-paste returns an immediate – and VERY hard to detect! – error.

The problem is Fluther. The ”-” sign in the formula turned into an em-dash in Fluther, and pastes that way back into Excel. Try it again, but when you get the error message – which you will! – simply edit the formula to replace the minus sign in “TODAY() – 14” with a proper minus sign, and it should work fine.

Thanks for pointing this out to me. It’s something I’ll have to watch for in the future.

THAT definitely worked. :) I had already figured out a lame work-around, where I had an IF test tag at the end of the line for “Open” or “Expired” items, and then just tested against that in a SUMIF. But yours is a much more elegant solution and I will probably use it anyway (as well as play with it for other stuff). Thank you again.

As ya’ll may have guessed I am very much a newcomer to Excel. I am in grad school atm, in fact, and am hoping to sneak in an Excel class before I graduate. :P