A COUNTIFS conundrum

[HelpNeeded47]

Hello Wise Excel Users,

I am in desperate need of your help, to both fix a shoddy bit of formula writing and put a stop the abuse my desk is receiving because of the trouble this is causing me.

The purpose of the code is to count items have been 'closed' today.

So say if Worker A has closed 2 items today I'd like a counter to show next to their name.

Currently I have it written as =COUNTIFS(L:L,"*Closed*",K:K,"*Worker A*",M:M,TODAY())

This is returning a nil count although items have been closed.

I'd be very grateful if someone can crack this for me.

All the best,
Village Idiot

 

[RoryA]

Are you sure there is no time part included in column M?

 

[HelpNeeded47]

Are you sure there is no time part included in column M?

Hi Rory, thanks for getting back to me. Not that I can see. It's just in 09-Aug format.

 

[Peter_SSs]

Can you post a small sample with dummy data with XL2BB? Columns K:M only

 

[HelpNeeded47]

Can you post a small sample with dummy data with XL2BB? Columns K:M only

I'm not able to use XL2BB, but I've attached images of a similar table. I'm also using a lists (image 2) sheet to utilise drop downs.

Sorry if this isn't a worthwhile substitution for XL2BB.

 

[Peter_SSs]

Well, your dates are left-aligned which might indicate text values, but seems to work anyway - provided you have the worker name correct.

22 08 09.xlsm
	K	L	M	N	O
1
2	A	Closed	9-Aug		2
3	A	Closed	9-Aug
4	A	Open
5
Sheet2 (4)

Cell Formulas
Range	Formula
O2	O2	=COUNTIFS(L:L,"*Closed*",K:K,"*A*",M:M,TODAY())

If you select one of those date cells, what do you see in the formula bar?

 

[HelpNeeded47]

Well, your dates are left-aligned which might indicate text values, but seems to work anyway - provided you have the worker name correct.

22 08 09.xlsm
	K	L	M	N	O
1
2	A	Closed	9-Aug		2
3	A	Closed	9-Aug
4	A	Open
5
Sheet2 (4)

Cell Formulas
Range	Formula
O2	O2	=COUNTIFS(L:L,"*Closed*",K:K,"*A*",M:M,TODAY())

If you select one of those date cells, what do you see in the formula bar?

View attachment 71176

Thank you Peter. This works! As a secondary question - is there a way of having the count show in one cell for each worker?

For example it'd show A 2; B 3; C 0 all in one cell.

Appreciate your help!

 

[Peter_SSs]

Thank you Peter. This works!

Hmm, it was just your original formula but with a changed worker name. providing your worker name was correct it should have worked for you from the beginning.

Two other comments

• I would recommend not using whole column references in these formulas. Just use enough rows to be sure of including all your data. below I have used down to row 100 but you can edit that up/down if required.
  
  
• Given the small sample data that you have shown, there is no need for the asterisk wildcard characters in the formula.

is there a way of having the count show in one cell for each worker?

For example it'd show A 2; B 3; C 0 all in one cell.

Like this?

22 08 09.xlsm
	K	L	M	N	O
1
2	A	Closed	10-Aug		A 3; B 2; C 0
3	A	Closed	10-Aug
4	A	Open	10-Aug
5	B	Closed	10-Aug
6	C	Open	10-Aug
7	B	Open	10-Aug
8	A	Closed	9-Aug
9	B	Closed	10-Aug
10	A	Closed	10-Aug
11
Count

Cell Formulas
Range	Formula
O2	O2	=LET(u,UNIQUE(FILTER(K2:K100,K2:K100<>"")),TEXTJOIN("; ",,u&" "&COUNTIFS(L2:L100,"Closed",K2:K100,u,M2:M100,TODAY())))

 

[HelpNeeded47]

Hmm, it was just your original formula but with a changed worker name. providing your worker name was correct it should have worked for you from the beginning.

Two other comments

• I would recommend not using whole column references in these formulas. Just use enough rows to be sure of including all your data. below I have used down to row 100 but you can edit that up/down if required.
  
  
• Given the small sample data that you have shown, there is no need for the asterisk wildcard characters in the formula.

Like this?

22 08 09.xlsm
	K	L	M	N	O
1
2	A	Closed	10-Aug		A 3; B 2; C 0
3	A	Closed	10-Aug
4	A	Open	10-Aug
5	B	Closed	10-Aug
6	C	Open	10-Aug
7	B	Open	10-Aug
8	A	Closed	9-Aug
9	B	Closed	10-Aug
10	A	Closed	10-Aug
11
Count

Cell Formulas
Range	Formula
O2	O2	=LET(u,UNIQUE(FILTER(K2:K100,K2:K100<>"")),TEXTJOIN("; ",,u&" "&COUNTIFS(L2:L100,"Closed",K2:K100,u,M2:M100,TODAY())))

This is fantastic, really appreciate your help. Miles better than the solutions I had worked up myself.
Cheers
Mr K

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.