Countif (remove duplicate) multi conditions.

[TH123]

Hi,

I have data as below. What I want to get is result same as last column. Could you pls advise which formul should be?
Logic: count each order how many time late No, how many time late Yes.

Thanks.

Ví dụ các hàm excel.xlsx
	R	S	T	U
23	Order#	Submit date	Late submission?	Result s/b
24	A	1-Dec	No	1
25	B	1-Dec	No	1
26	A	2-Dec	Yes	1
27	B	2-Dec	Yes	2
28	C	1-Dec	No	1
29	B	3-Dec	Yes	0
30	C	2-Dec	Yes	1
31	A	1-Dec	No	0
Sheet1

 

[StephenCrump]

Based on the question, I would have guessed:

	R	S	T	U	V
23	Order#	Submit date	Late submission?	Result s/b
24	A	1 Dec 2023	No	1	2
25	B	1 Dec 2023	No	1	1
26	A	2 Dec 2023	Yes	1	1
27	B	2 Dec 2023	Yes	2	2
28	C	1 Dec 2023	No	1	1
29	B	3 Dec 2023	Yes	0	2
30	C	2 Dec 2023	Yes	1	1
31	A	1 Dec 2023	No	0	2
Sheet1

Cell Formulas
Range	Formula
V24:V31	V24	=COUNTIFS(R$24:R$31,R24,T$24:T$31,T24)

But that doesn't matcch your results where highlighted.

If your counts are correct, can you please explain the logic?

 

[TH123]

Based on the question, I would have guessed:

	R	S	T	U	V
23	Order#	Submit date	Late submission?	Result s/b
24	A	1 Dec 2023	No	1	2
25	B	1 Dec 2023	No	1	1
26	A	2 Dec 2023	Yes	1	1
27	B	2 Dec 2023	Yes	2	2
28	C	1 Dec 2023	No	1	1
29	B	3 Dec 2023	Yes	0	2
30	C	2 Dec 2023	Yes	1	1
31	A	1 Dec 2023	No	0	2
Sheet1

Cell Formulas
Range	Formula
V24:V31	V24	=COUNTIFS(R$24:R$31,R24,T$24:T$31,T24)

But that doesn't matcch your results where highlighted.

If your counts are correct, can you please explain the logic?

Hi StephenCrump,

Thanks for your feecback. Logic here is: count each order how many time late No, how many time late Yes.
Example order#B have 2 times late yes (row#27 & row#29) however I just want to put 2 at row#27 while row#29 s/b 0 then when I create report, it will be shown total 2 times late yes only i/o 4

 

[StephenCrump]

OK, does that mean U24 should be 2, rather than the 1 you have shown, because A/No occurs twice?

You could put this into a column formula, with 0 for repeat occurrences, but wouldn't you be better with a summary table?

	R	S	T
23	Order#	Submit date	Late submission?
24	A	1 Dec 2023	No
25	B	1 Dec 2023	No
26	A	2 Dec 2023	Yes
27	B	2 Dec 2023	Yes
28	C	1 Dec 2023	No
29	B	3 Dec 2023	Yes
30	C	2 Dec 2023	Yes
31	A	1 Dec 2023	No
32
33
34		Yes	No
35	A	1	2
36	B	2	1
37	C	1	1
Sheet1

Cell Formulas
Range	Formula
R35:R37	R35	=UNIQUE(R24:R31)
S35:T37	S35	=COUNTIFS($R$24:$R$31,$R35,$T$24:$T$31,S$34)
Dynamic array formulas.

 

[TH123]

OK, does that mean U24 should be 2, rather than the 1 you have shown, because A/No occurs twice?

You could put this into a column formula, with 0 for repeat occurrences, but wouldn't you be better with a summary table?

	R	S	T
23	Order#	Submit date	Late submission?
24	A	1 Dec 2023	No
25	B	1 Dec 2023	No
26	A	2 Dec 2023	Yes
27	B	2 Dec 2023	Yes
28	C	1 Dec 2023	No
29	B	3 Dec 2023	Yes
30	C	2 Dec 2023	Yes
31	A	1 Dec 2023	No
32
33
34		Yes	No
35	A	1	2
36	B	2	1
37	C	1	1
Sheet1

Cell Formulas
Range	Formula
R35:R37	R35	=UNIQUE(R24:R31)
S35:T37	S35	=COUNTIFS($R$24:$R$31,$R35,$T$24:$T$31,S$34)
Dynamic array formulas.

U24 & U31 have same day so just count 1 time only because 1 day just allow 1 time submission and if fail then need re-submit. But finally count 1 time only. Sorry for lack of information provide.
Actually late submission in my real data is not only yes or no, there are a lot of conditions and need to count percentage of each condition per each submission time per each order so summary table doesn't work well (have tried). Do you have any idea to put in data source following result at column U? Sorry for any unclear explanation.