Use COUNTIF with OR condition

[MeredithS]

Hello, I am stumped.

I have a spreadsheet with three date columns. I want to create a tally that will count each row only once if any of the three columns contains a date greater than 7/1/2011. Here is my formula for tallying just one column:

=COUNTIF('Deduped Lead Contact Data'!H:H,">=07/01/2011")

The only threads I can find talk about adding COUNTIFs, but I don't want to count a row twice if two of the columns contain a date greater than 7/1/2011.

Any insight would be greatly appreciated! Thanks,
Meredith

 

[Jose_Chasez]

If you're ok adding a helper column to your spreadsheet, you could:

1. Add a formula that tests if your data condition is met for a particular row. For example, if your dates were in columns A - C, you could insert the following formula in column D, and drag down. It will return "Yes" if your date condition is met.

=IF(MAX(A1:C1)>DATE(2011,7,1),"Yes","No")

2. Next, use a COUNTIF formula to count the number of "Yes" values in column D

 

[T. Valko]

Hello, I am stumped.

I have a spreadsheet with three date columns. I want to create a tally that will count each row only once if any of the three columns contains a date greater than 7/1/2011. Here is my formula for tallying just one column:

=COUNTIF('Deduped Lead Contact Data'!H:H,">=07/01/2011")

The only threads I can find talk about adding COUNTIFs, but I don't want to count a row twice if two of the columns contain a date greater than 7/1/2011.

Any insight would be greatly appreciated! Thanks,
Meredith

One way...

Book1
	A	B	C	D	E
2	12/15/2008	8/1/2000	5/3/2005	_	7/1/2011
3	9/20/2012	9/20/2011	8/19/2013	_	5
4	9/29/2002	9/24/2013	6/21/2010	_	_
5	6/15/2009	9/21/2008	3/18/2005	_	_
6	3/9/2013	12/8/2004	3/28/2010	_	_
7	5/1/2000	2/2/2000	10/10/2009	_	_
8	2/20/2004	12/12/2007	3/29/2012	_	_
9	1/19/2014	11/26/2005	5/18/2013	_	_
10	6/29/2011	3/16/2004	6/10/2010	_	_
Sheet1

This formula entered in E3:

=SUMPRODUCT(SIGN((A2:A10>E2)+(B2:B10>E2)+(C2:C10>E2)))

 

[MeredithS]

Thanks both! Used T. Valko's formula since I don't want to add a helper column and it worked like a charm!!

 

[T. Valko]

Thanks both! Used T. Valko's formula since I don't want to add a helper column and it worked like a charm!!

You're welcome. Thanks for the feedback!