COUNTIFS Function

Tikey

Board Regular
Joined
Jan 30, 2014
Messages
97
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jan 30, 2014
Messages
97
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jan 30, 2014
Messages
97
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
@ 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
Joined
Jan 30, 2014
Messages
97
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@ 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

MrExcel MVP
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jan 30, 2014
Messages
97
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,628
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top