Count Non Blanks & Blanks

[CJones6]

Hi There,

I'm looking for help to write a few formulas please.

In my data set, column P,Q,R&S contains over 500 rows of data, some of the cells are populated with a date others are blank.

Column P captures the date that a work activity moved into stage 1.
Column Q captures the date work started.
Column R captures the date work ended
Column S captures the date work exited stage 1.

I need 4 formulas in total. once I understand the logic I can then adapt these, as have over 8 stages to calculate.

I have looked at a combination of counta, countblank and countifs but I can't work the logic for even the first formula. I haven't used excel for the last 4 years so this is definitely a challenge for me.

Formula 1
If column p has a date I need a count of column q without date. This will tell me count of work which has gone into stage 1 but yet to start.

Formula 2
if column p has a date I need a count of column q with a date but column r does not have a date. So work has gone in and started but not finished.

Formula 3
if column p has a date I need a count of column q with a date but column r does have a date and column s doesn't. So work has gone in and started, finished and not exited.

Formula 4
if column R has a date I need a count if column s has a date. So work has finished and exited.


Many thanks for reading, hope it makes sense!

 

[etaf]

I have just used countifs()
on 2 formulas - do they work on all your data - as i think all the formulas needed can probably be done with a countifs()

Formula 1
If column p has a date I need a count of column q without date. This will tell me count of work which has gone into stage 1 but yet to start.

I assume if Column P does NOT have a date then its blank
So you are counting the cells where column P is NOT blank and Column Q is blank

Book3
	P	Q	R
3	1/1/21		6
4	1/2/21	1
5	1/3/21	2
6	1/4/21
7	1/5/21	3
8	1/6/21	4
9	1/7/21	6
10	1/8/21
11	1/9/21	7
12	1/10/21	8
13	1/11/21
14	1/12/21	9
15	1/13/21
16	1/14/21	10
17
18
19	1/17/21
Sheet2

Cell Formulas
Range	Formula
R3	R3	=COUNTIFS(P3:P19,"<>",Q3:Q19,"")

Formula 2
if column p has a date I need a count of column q with a date but column r does not have a date. So work has gone in and started but not finished.

Same sort of formula extended for R

Book3
	P	Q	R	S
3	1/1/21			4
4	1/2/21	1
5	1/3/21	2	1
6	1/4/21
7	1/5/21	3
8	1/6/21	4	1
9	1/7/21	6	2
10	1/8/21
11	1/9/21	7
12	1/10/21	8	3
13	1/11/21
14	1/12/21	9	1
15	1/13/21
16	1/14/21	10
17
18
19	1/17/21
Sheet2

Cell Formulas
Range	Formula
S3	S3	=COUNTIFS(P3:P19,"<>",Q3:Q19,"<>",R3:R19,"")

 

[etaf]

Formula 3
if column p has a date I need a count of column q with a date but column r does have a date and column s doesn't. So work has gone in and started, finished and not exited.

=COUNTIFS(P3:P19,"<>",Q3:Q19,"<>",R3:R19,"<>", S3:S19, "")

Formula 4
if column R has a date I need a count if column s has a date. So work has finished and exited.

=COUNTIFS(R3:R19,"<>", S3:S19, "<>")

 

[CJones6]

Hi,
Thank you for the reply! I have honestly spent hours trying to do this on my own!!

I have worked these formulas into a smaller workbook so I can test out the logic and I'm good up to formula 4.
I have written the formulas to select the columns e.g =COUNTIFS(P:P,"<>",Q:Q,"") and that works great until formula 4.
I have written formula 4 as =COUNTIFS(R:R,"<>", S:S, "<>") and I'm provided with a count of 3 but when written as =COUNTIFS(R2:R22,"<>", S2:S22, "<>") I'm provided with the correct count of 2.
Not sure why formula 4 would calculate a different count. I converted the data to a table to see if this changed the count but it stayed the same. Sorry to come back and ask another question.
Many Thanks!

 

[etaf]

no problem coming back
Whats in row1 - headings, which may explain and would not be blank and so would be included in the count

 

[Sufiyan97]

This formula counts headers
=COUNTIFS(R:R,"<>", S:S, "<>")

That's why you are getting 3

This Don't
=COUNTIFS(R2:R22,"<>", S2:S22, "<>")

 

[CJones6]

Ok, thank you
I get it now, so the non blank count is working great because the header is populated. So I l need to exclude row 1 from the formula range in my masterfile!
Thank you again for help!!

 

[etaf]

you are welcome