Count based on one column being blank and one being not blank

[bchez]

I can't find this or figure it out, and it should be simple. I'm embarrassed to ask. I have a spreadsheet where we receive a request and fill in a date when we receive the request. See column A below. When we complete the request, we log the date in Column B. I need the count of all incomplete requests, based on their being a date in column A but no corresponding date in Column B. How do I do this? The answer in my example should be 2. The formula would go in B2 - letters and numbers below simulate row and col headings. Thank you in advance if you know this.

	A	B	C
1	Date received	Date completed	Customer name
2	6/14/17		Jones
3	6/14/17	6/21/17	Smith
4	6/14/17	6/18/17	Thomas
5	6/15/17		Mills

<tbody>
</tbody>

 

[marious]

=countblank(b1:5)

 

[bchez]

Thank you, but that doesn't take into account column A. If A2 is not blank but B2 is blank, I want it to count B2. If A3 is not blank but B3 is blank, I want it to count B3 etc. Make sense? Thank you again!

 

[mikerickson]

=SUMPRODUCT(COUNTA(A1:A10)-COUNTA(B1:B10))

will return the number of rows in A1:B10 where column A has a value and column B does not.

(The formula assumes that it is never the case that A is blank and B is filled)

I just realized that, with that assumption, the SUMPRODUCT is not needed

=COUNTA(A1:A10)-COUNTA(B1:B10))

 

[Eric W]

Perhaps:

=COUNTIFS(A2:A5,"<>",B2:B5,"")

 

[bchez]

=SUMPRODUCT(COUNTA(A1:A10)-COUNTA(B1:B10))

will return the number of rows in A1:B10 where column A has a value and column B does not.

(The formula assumes that it is never the case that A is blank and B is filled)

I just realized that, with that assumption, the SUMPRODUCT is not needed

=COUNTA(A1:A10)-COUNTA(B1:B10))

Thank you Mike. This works nicely. I modified it slightly to read =COUNTA($A:$A)-COUNTA($B:$B) and it seems to work. Nicely done. I would not have thought of COUNTA
Also your assumption in quotes above is entirely true as well. Very nice of you to assist in this matter.

 

[bchez]

Thanks Eric - this formula, like Mike's above, also works very well. Very much appreciated. At first my boss wanted me to find how many were incomplete by date, for example, how many are incomplete on June 3, 2017. I believe this is impossible without VBA. I am much obliged for your considerate and effective help. Thank you

 

[Eric W]

Actually, it's pretty easy to count the incomplete ones by date:

Book1
	A	B	C	D	E
1	Date Received	Date Completed	Customer Name
2	1/1/2017	1/2/2017	Amy	2
3	2/1/2017		Bob	1
4	3/1/2017	3/2/2017	Cathy	1	4/1/2017
5	4/1/2017	4/2/2017	Doug
6	4/1/2017		Eva
7	4/1/2017	4/4/2017	Frank
Sheet7

Cell Formulas
Range	Formula
D2	=COUNTIFS(A:A,"<>",B:B,"")
D3	=COUNTIFS(A:A,DATE(2017,4,1),B:B,"")
D4	=COUNTIFS(A:A,E4,B:B,"")

The D2 formula is my original from post #5, which shows a count of 2 (row 3 and row 6). D3 shows how to put a date in it, and D4 shows how to put the date in another cell. These both just count row 6.

Glad we could help.

 

[bchez]

Actually, it's pretty easy to count the incomplete ones by date:

	A	B	C	D	E
1	Date Received	Date Completed	Customer Name
2	1/1/2017	1/2/2017	Amy	2
3	2/1/2017		Bob	1
4	3/1/2017	3/2/2017	Cathy	1	4/1/2017
5	4/1/2017	4/2/2017	Doug
6	4/1/2017		Eva
7	4/1/2017	4/4/2017	Frank

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula D2 =COUNTIFS(A:A,"<>",B:B,"") D3 =COUNTIFS(A:A,DATE(2017,4,1),B:B,"") D4 =COUNTIFS(A:A,E4,B:B,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The D2 formula is my original from post #5, which shows a count of 2 (row 3 and row 6). D3 shows how to put a date in it, and D4 shows how to put the date in another cell. These both just count row 6.

Glad we could help.

Thank you. I will try these out next work shift. Cheers to you!

 

[marious]

sorry about that this one will work =SUMPRODUCT((A2:A5<>"")*(B2:B5=""))