COUNTIFS Function

[Tikey]

I am using the following formula to count the number of occurences of a date so long as a value in a corresponding column is >0.


=COUNTIFS(Records!$F$2:$F$40000,">=1992-1-1",Records!$F$2:$F$40000,"<1993-1-1",Records!$I$2:$I$40000,">0")

This works fine, but I wish to expand the range which needs to be >0 to include a further 2 columns - Records!$I$2:$K$40000. If I do this I get a #VALUE ! error.

Can anyone offer a soultion to this, any help appreciated.

 

[JoeMo]

Untested, but you can try:

=COUNTIFS(Records!$F$2:$F$40000,">=1992-1-1",Records!$F$2:$F$40000,"<1993-1-1",Records!$I$2:$I$40000,">0",Records!$J$2:$J$40000,">0",Records!$K$2:$K$40000,">0")

 

[Tikey]

Untested, but you can try:

=COUNTIFS(Records!$F$2:$F$40000,">=1992-1-1",Records!$F$2:$F$40000,"<1993-1-1",Records!$I$2:$I$40000,">0",Records!$J$2:$J$40000,">0",Records!$K$2:$K$40000,">0")

Thanks for the reply. Since posting I have tried this but it requires a value >0 in all 3 columns I,J,K before it will count. It needs to count when there is a value of >0 in any of the 3 columns.

 

[JoeMo]

Thanks for the reply. Since posting I have tried this but it requires a value >0 in all 3 columns I,J,K before it will count. It needs to count when there is a value of >0 in any of the 3 columns.

That wasn't clear from your prior post. See if this does what you want:

=SUMPRODUCT((Records!$F$2:$F$40000>=1992-1-1)*(Records!$F$2:$F$40000<1993-1-1)*((Records!$I$2:$I$40000>0)+(Records!$J$2:$J$40000>0)+(Records!$K$2:$K$40000>0)))

 

[Tikey]

Thanks again for your reply, unfortunately that doesn't work either.

 

[Aladin Akyurek]

@ JoeMo

You still need to double quote the date specs and coerce...

=SUMPRODUCT((Records!$F$2:$F$40>="1992-1-1"+0)*(Records!$F$2:$F$40<"1993-1-1"+0)*((Records!$I$2:$I$40>0)+(Records!$J$2:$J$40>0)+(Records!$K$2:$K$40>0)))

 

[Tikey]

Thanks for the reply, but this is summing all the values in the I,J &K columns.

Please see the start of the thread which hopefully highlights what I am trying to achieve.

 

[JoeMo]

@ JoeMo

You still need to double quote the date specs and coerce...

=SUMPRODUCT((Records!$F$2:$F$40>="1992-1-1"+0)*(Records!$F$2:$F$40<"1993-1-1"+0)*((Records!$I$2:$I$40>0)+(Records!$J$2:$J$40>0)+(Records!$K$2:$K$40>0)))

Good catch Aladin, thanks.

 

[JoeMo]

Thanks for the reply, but this is summing all the values in the I,J &K columns.

Please see the start of the thread which hopefully highlights what I am trying to achieve.

OK that's different from what I was addressing. Try this array formula and be sure to confirm using ctrl+shift+enter.

=SUM(IF((Records!$F$2:$F$40>="1992-1-1"+0)*(Records!$F$2:$F$40<"1993-1-1"+0)*((Records!$I$2:$I$40>0)+(Records!$J$2:$J$40>0)+(Records!$K$2:$K$40>0)),1,0))

 

[Tikey]

Cheers, that's now working, thank you for all your time and effort on this.

You may be a little older, but you still know a lot.