Modification to COUNTIFS formula

Is there a way to change this formula so that it will only calculate the COUNTIFS on data that falls in the month of May? The dates in the data set are in the range V14:V100000


=SUM(COUNTIFS($Q$14:$Q$10000,{"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8","ii1x9","ii1x10"},$K$14:$K$10000,{">0"}))

=SUM(IF(MONTH(V14:V100000)=5,1,0))

Array formula, enter the above and then use Control, Shift, Enter.

I think you'd have to change it to a Sumproduct formula. Countifs can't manipulate the data in ranges.
=Sumproduct(($Q$14:$Q$10000={"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8","ii1x9","ii1x1 0"})*($K$14:$K$10000>0)*(Month($V$14:$V$100000)=5)) should work, I think.

All ranges have to be the same size - last range needs to be 10000 not 100000, i.e.

=SUMPRODUCT(($Q$14:$Q$10000={"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8", "ii1x9","ii1x10"})*($K$14:$K$10000>0)*(MONTH($V$14:$V$10000)=4))

You can modify your COUNTIFS approach but you need to add two more criteria, i.e.

=SUM(COUNTIFS($Q$14:$Q$10000,{"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8","ii1x9","ii1x10"},$K$14:$K$10000,">0",$V$14:$V$10000,">="&DATE(2011,4,1),$V$14:$V$10000,"<="&DATE(2011,4,30)))

The first formula will count April dates in any year, the second is restricted to 2011

The range you used in V had one too many zeroes and I didn't catch the error when I inserted into the formula. Make it go through V10000 instead and it comes up with 8.

For my own edification, which would be faster, adding two criteria to the Countifs or changing to Sumproduct?

Without testing I suspect that COUNTIFS will be faster, typically a COUNTIF/COUNTIFS formula can be 5-10 times faster than the equivalent SUMPRODUCT.....

Yes you can add date ranges exactly as I did with the COUNTIFS version

....btw, are you counting all instances of values beginning with "ii1x" in column Q....or are there instances that don't appear in the list like ii1x1 or ii1x22?

Originally Posted by rhudgins

Is there a way to use the SUMPRODUCT formula?

Almost exactly the same as the other one: This was the original

=SUMPRODUCT(($Q$14:$Q$10000={"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8", "ii1x9","ii1x10"})*($K$14:$K$10000>0)*(MONTH($V$14:$V$10000)=4))

just remove >0

Removing >0 is producing a #VALUE error. Below, I constructed this SUMIFS formula using the COUNTIFS example that you gave me above. What would the equivalent SUMPRODUCT formula be to calculate the same result? The reason I ask is because I want to avoid having to constantly change the date ranges inside the SUMIFS formula

=SUM(SUMIFS($K$14:$K$10000,$Q$14:$Q$10000,{"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8","ii1x9","ii1x1 0"},$V$14:$V$10000,">="&DATE(2011,6,1),$V$14:$V$10000,"<="&DATE(2011,6,30)))

OK, I took a closer look.....

normally the SUMPRODUCT would work but in column K because you have some formulas returning "" those are treated as text and they cause #VALUE! errors in that formula.

3 options

1) Leave the data "as is" and use this "array formula"

=SUM(IF(($Q$14:$Q$10000={"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8","ii1x9","ii1x10"})*(MONTH($V$14:$V$10000)=4),$K$14:$K$10000))

confirmed with CTRL+SHIFT+ENTER

2) Change the formula in K14 copied down to this

=IF(D14="",0,J14+D14)

and then the SUMPRODUCT version will work for you, i.e.

=SUMPRODUCT(($Q$14:$Q$10000={"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8","ii1x9","ii1x10"})*($K$14:$K$10000)*(MONTH($V$14:$V$10000)=4))

[If you want to display a blank rather than $0 just custom format to do so]

3) You can use the SUMIFS formula with a reference to a cell, e.g. put the first of the month in Z2 and use this version

=SUM(SUMIFS($K$14:$K$10000,$Q$14:$Q$10000,{"ii1x2","ii1x3","ii1x4","ii1x5","ii1x6","ii1x7","ii1x8"," ii1x9","ii1x1 0"},$V$14:$V$10000,">="&Z2,$V$14:$V$10000,"<="&EOMONTH(Z2,0)))

Now you only need to change a single date