Logic operator in array section of sum(countifs - ?????

[spydey]

Can I use logic operators in the array section of a sum(countifs formula?

Example:

=Sum(Countifs(item.range,A3,location.range,B3,date.1.range,{"<"&A1,">"&Eomonth(A1,0)})

A1 is a date, typically the 1st of any given month.

I have tried it and it doesn't seem to work as expected.

Any thoughts, insight, ideas?

Thanks!

-Spydey

 

[UniMord]

You are trying to enter what is called an "Array Constant". By definition, it can only contain constants. No references or formulas are allowed. To solve your problem, you want to enter 2 separate pairs of date column and criterion, to get your desired result.

That said, your logic looks a little faulty. You're asking for < A1 and > EOMONTH(A1, 0), that will never return anything, because a value can't be both less than and greater than A1.

 

[Eric W]

First, if you are using an array constant, you can only use constants within it. {1,2} or {"cat","dog"}, {"<1",">10"} all are OK, but {"<"&A1,">"&Eomonth(A1,0)} is not, because both of the elements in it are formulas.

Next, depending on what you want your formula to do, there still might be a way to do it. It appears you want to count all the items in the list that are not in the month of the date in A1. This should work:

=Countifs(item.range,A3,location.range,B3) - Countifs(item.range,A3,location.range,B3,date.1.range,">="&A1,date.1.range,"<="&EOMONTH(A1,0))

which counts everything in the range, then subtracts the rows within the given month.

If that doesn't work, then you can usually use SUMPRODUCT instead, which is more flexible, but has a different format, and whole column references are discouraged.

 

[barry houdini]

Try this version

=SUMPRODUCT(COUNTIFS(item.range,A3,location.range,B3,date.1.range,IF({1,0},"<"&A1,">"&EOMONTH(A1,0))))