Countif (remove duplicate) multi conditions.

TH123

New Member
Joined
Nov 15, 2022
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
RSTU
23Order#Submit dateLate submission?Result s/b
24A1-DecNo1
25B1-DecNo1
26A2-DecYes1
27B2-DecYes2
28C1-DecNo1
29B3-DecYes0
30C2-DecYes1
31A1-DecNo0
Sheet1
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Based on the question, I would have guessed:

RSTUV
23Order#Submit dateLate submission?Result s/b
24A1 Dec 2023No12
25B1 Dec 2023No11
26A2 Dec 2023Yes11
27B2 Dec 2023Yes22
28C1 Dec 2023No11
29B3 Dec 2023Yes02
30C2 Dec 2023Yes11
31A1 Dec 2023No02
Sheet1
Cell Formulas
RangeFormula
V24:V31V24=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?
 
Upvote 1
Based on the question, I would have guessed:

RSTUV
23Order#Submit dateLate submission?Result s/b
24A1 Dec 2023No12
25B1 Dec 2023No11
26A2 Dec 2023Yes11
27B2 Dec 2023Yes22
28C1 Dec 2023No11
29B3 Dec 2023Yes02
30C2 Dec 2023Yes11
31A1 Dec 2023No02
Sheet1
Cell Formulas
RangeFormula
V24:V31V24=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
 
Upvote 0
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?

RST
23Order#Submit dateLate submission?
24A1 Dec 2023No
25B1 Dec 2023No
26A2 Dec 2023Yes
27B2 Dec 2023Yes
28C1 Dec 2023No
29B3 Dec 2023Yes
30C2 Dec 2023Yes
31A1 Dec 2023No
32
33
34YesNo
35A12
36B21
37C11
Sheet1
Cell Formulas
RangeFormula
R35:R37R35=UNIQUE(R24:R31)
S35:T37S35=COUNTIFS($R$24:$R$31,$R35,$T$24:$T$31,S$34)
Dynamic array formulas.
 
Upvote 0
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?

RST
23Order#Submit dateLate submission?
24A1 Dec 2023No
25B1 Dec 2023No
26A2 Dec 2023Yes
27B2 Dec 2023Yes
28C1 Dec 2023No
29B3 Dec 2023Yes
30C2 Dec 2023Yes
31A1 Dec 2023No
32
33
34YesNo
35A12
36B21
37C11
Sheet1
Cell Formulas
RangeFormula
R35:R37R35=UNIQUE(R24:R31)
S35:T37S35=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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top