COUNTIFS Function

Tikey

Board Regular
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.

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

JoeMo

MrExcel MVP
Untested, but you can try:
Code:
=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

Board Regular
Untested, but you can try:
Code:
=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

MrExcel MVP
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:
Code:
=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

MrExcel MVP
@ 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

Board Regular

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

MrExcel MVP
@ 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)))

JoeMo

MrExcel MVP
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.
Code:
=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

Board Regular
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.

Replies
3
Views
102
Replies
5
Views
178
Replies
0
Views
31
Replies
18
Views
642
Replies
4
Views
435