Count only if immediate right cell is blank...sort of.

[kitteyrin]

So I have a mess I'm trying to work with. What I'm trying to do is if column G or H has a date in it (isn't blank), count the cell as 1 and return the number. While I don't have an issue with that part, my complication comes in if there's a value (not blank) in column H- that is, it counts it twice. What I need to do is only count it only once even if H has something written in it. However, sometimes only H column is filled, and I still need that column to be counted. Further, I also need it to automatically subtract from the total if column J has the text "Rem'd", "Redefault", or "Disputing" in it.

Here are the various equations I've tried, starting with my original one:

=COUNTA(G2:H2019)-(COUNTIF(J:J,"*REDEFAULT*")+COUNTIF(J:J,"*DISPUTING*")+COUNTIF(J:J,"*REM'D*"))

=COUNTIF(G2:G2012,AND(G2:G2012="<>"&"",H2:H2012=""))

=COUNTIF(G2:G2012,AND(G2:G2012="<>"&"",H2:H2012=""))-(COUNTIF(J:J,"*REDEFAULT*")+COUNTIF(J:J,"*DISPUTING*")+COUNTIF(J:J,"*REM'D*"))

=COUNTA(AND(G2:G2012="<>"&"",H2:H2012=""))-(COUNTIF(J:J,"*REDEFAULT*")+COUNTIF(J:J,"*DISPUTING*")+COUNTIF(J:J,"*REM'D*"))

In order, the above equations return 4, 0, -16, and -15

My data is below, and what I need it to return is 1 for the displayed data, or if all the notes in J is removed, 17.

G	H	I	J
		x	February Complete
		x
02/04/2016		x	Rem'd
02/05/2016	02/08/2016		Rem'd
02/05/2016	02/08/2016		Rem'd
02/04/2016		x	Rem'd
		x
02/16/2016		x	Rem'd
		x
02/17/2016		x	Rem'd
02/17/2016		x	Rem'd
02/05/2016		x	Rem'd
02/18/2016		x	Rem'd
02/19/2016		x	Rem'd
		x
		x
02/24/2016		x	Rem'd
02/24/2016	02/26/2016
02/23/2016		x	Rem'd
02/24/2016		x	Rem'd
02/18/2016			Disputing
02/26/2016		x	Rem'd
		x
		x
		x
	02/04/2016		Redefault
		x
		x

Could anyone help me please? I'm willing to provide more information as needed, considering I'm not sure what other information may possibly be required to solve my issue.

 

[easy2understandexcel]

=MIN(1, COUNTA(G1:H1)) and then sum the column that you put this formula in

Hope that helps

 

[kitteyrin]

That formula returns a 1 for the table I put in, but if I remove any of the "Rem'd" on line J it doesn't change to 2.

 

[easy2understandexcel]

That formula returns a 1 for the table I put in, but if I remove any of the "Rem'd" on line J it doesn't change to 2.

Maybe Im not getting what you're trying to do...? Can you check over your original post and clarify?

 

[jtakw]

Hi,

Not elegant, but does what you requested:

Excel 2010
	G	H	I	J	K	L
1			x	February Complete		1
2			x
3	2/4/2016		x	Rem'd
4	2/5/2016	2/8/2016		Rem'd
5	2/5/2016	2/8/2016		Rem'd
6	2/4/2016		x	Rem'd
7			x
8	2/16/2016		x	Rem'd
9			x
10	2/17/2016		x	Rem'd
11	2/17/2016		x	Rem'd
12	2/5/2016		x	Rem'd
13	2/18/2016		x	Rem'd
14	2/19/2016		x	Rem'd
15			x
16			x
17	2/24/2016		x	Rem'd
18	2/24/2016	2/26/2016
19	2/23/2016		x	Rem'd
20	2/24/2016		x	Rem'd
21	2/18/2016			Disputing
22	2/26/2016		x	Rem'd
23			x
24			x
25			x
26		2/4/2016		Redefault
27			x
28			x
Sheet1

Cell Formulas
Range	Formula
L1	=COUNTIFS(H1:H28,"",G1:G28,"<>"&"")+COUNTIFS(I1:I28,"",H1:H28,"<>"&"")-SUM(COUNTIF(J1:J28,{"Rem'd","Redefault","Disputing"}))