Countifs with more criteria returns 0

L

LejokeR

New Member
Joined
Oct 10, 2022
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Hello,
Can you help me?
I'm designing a dashboard . In fact, dashboard's cells content more conditions and so, i use countifs function . However, when i insert this function, it return zero.

So, i don't evolve. Example: =COUNTIFS(Operations!B4:B815,$B$9,Operations!G4:G815,$C$9,Operations!M4:M815,$F$8,Operations!L4:L815,$E$9)
Bellow, i attach file.

Wait for solution
Report_Observation_CVS.xlsx
BCDEFGHI
1RAPPORT DES OPERATIONS DE CONTROLE: BLUE ENERGY - SNEL
2 Statistique en %
3Remise en état 10%
4Deconnecter56%
5Installer76%
6
7CVSREFUSNbrACTIONSNOMBRE DE COMPTEURS INSTALLER PAR
8Par agent BEPar agent SNELPar agents BE et SNELSNEL / Menaces
9DELVAUXRefus de remise en état 18Remise en état 0
10
11Deconnecter0
12
13Refus d'accès au contrôle22Controler
14Remise en état 0
15Deconnecter0
16Refus d'installation93Installer0
17Deconnecter0
Rapport
Cell Formulas
RangeFormula
F9F9=COUNTIFS(Operations!B4:B815,$B$9,Operations!G4:G815,$C$9,Operations!M4:M815,$F$8,Operations!L4:L815,$E$9)
F11F11=COUNTIFS(Operations!B6:B817,$B$9,Operations!G6:G817,"Refus de remise en état ",Operations!M6:M817,"Par agent BE",Operations!L6:L817,"Remise en état")
D9D9=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,Rapport!C9)
D13D13=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,"=Refus d'accès au contrôle")
D16D16=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,"=Refus d'installation")
F14:F17F14=COUNTIFS(Operations!B8:B819,$B$9,Operations!G8:G819,"Refus de remise en état ",Operations!M8:M819,"Par agent BE",Operations!L8:L819,"Remise en état")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:I5Other TypeDataBarNO
H3Other TypeIcon setNO
Cells with Data Validation
CellAllowCriteria
B9:B17List=Societes!$A$2:$A$6
F8List=Societes!$D$2:$D$5
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Without the Operations sheet data, the only thing I can suggest offhand is that there really are no matches. One possible reason is leading or trailing spaces - for example, there is a space on the end of the text in E9.
 
Upvote 0
LejokeR said:
Hello,
Can you help me?
I'm designing a dashboard . In fact, dashboard's cells content more conditions and so, i use countifs function . However, when i insert this function, it return zero.

So, i don't evolve. Example: =COUNTIFS(Operations!B4:B815,$B$9,Operations!G4:G815,$C$9,Operations!M4:M815,$F$8,Operations!L4:L815,$E$9)
Bellow, i attach file.

Wait for solution
Report_Observation_CVS.xlsx
BCDEFGHI
1RAPPORT DES OPERATIONS DE CONTROLE: BLUE ENERGY - SNEL
2 Statistique en %
3Remise en état 10%
4Deconnecter56%
5Installer76%
6
7CVSREFUSNbrACTIONSNOMBRE DE COMPTEURS INSTALLER PAR
8Par agent BEPar agent SNELPar agents BE et SNELSNEL / Menaces
9DELVAUXRefus de remise en état 18Remise en état 0
10
11Deconnecter0
12
13Refus d'accès au contrôle22Controler
14Remise en état 0
15Deconnecter0
16Refus d'installation93Installer0
17Deconnecter0
Rapport
Cell Formulas
RangeFormula
F9F9=COUNTIFS(Operations!B4:B815,$B$9,Operations!G4:G815,$C$9,Operations!M4:M815,$F$8,Operations!L4:L815,$E$9)
F11F11=COUNTIFS(Operations!B6:B817,$B$9,Operations!G6:G817,"Refus de remise en état ",Operations!M6:M817,"Par agent BE",Operations!L6:L817,"Remise en état")
D9D9=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,Rapport!C9)
D13D13=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,"=Refus d'accès au contrôle")
D16D16=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,"=Refus d'installation")
F14:F17F14=COUNTIFS(Operations!B8:B819,$B$9,Operations!G8:G819,"Refus de remise en état ",Operations!M8:M819,"Par agent BE",Operations!L8:L819,"Remise en état")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:I5Other TypeDataBarNO
H3Other TypeIcon setNO
Cells with Data Validation
CellAllowCriteria
B9:B17List=Societes!$A$2:$A$6
F8List=Societes!$D$2:$D$5
Click to expand...

RoryA said:
Without the Operations sheet data, the only thing I can suggest offhand is that there really are no matches. One possible reason is leading or trailing spaces - for example, there is a space on the end of the text in E9.
Click to expand...
Please RoryA , can explain clearly?
Let me to share file operation.
 
Upvote 0
LejokeR said:
Please RoryA , can explain clearly?
Let me to share file operation.
Click to expand...
Report_Observation_CVS.xlsx
L
3Résultat
7Remise en état
8Remise en état
9Remise en état
43Remise en état
Operations
Cells with Data Validation
CellAllowCriteria
L4:L43List=Societes!$C$2:$C$5

Report_Observation_CVS.xlsx
M
3Executer
7Par agent BE
8Par agent BE
9Par agent BE
43Par agent BE
Operations
Cells with Data Validation
CellAllowCriteria
M4:M43List=Societes!$D$2:$D$5
 
Upvote 0
Report_Observation_CVS.xlsx
B
3CVS
7GOMBE
8GOMBE
9GOMBE
Operations
Cells with Data Validation
CellAllowCriteria
B4:B9List=Societes!$A$2:$A$6

Report_Observation_CVS.xlsx
G
3Raison de mise en demeure
7Refus de remise en état
8Refus de remise en état
9Refus de remise en état
Operations
Cells with Data Validation
CellAllowCriteria
G4:G9List=Societes!$B$2:$B$4
 
Upvote 0
Your value in B9 doesn't match GOMBE, so the results should be 0. If I alter B9 to GOMBE, then the calculation for row 9 is correct.
 
Upvote 0
My point is that 0 is the correct answer for the sample data you provided. If I change B9 to match the data set you provided, the count for row 9 updates to 3 which is correct.
 
Upvote 0
Report_Observation_CVS.xlsx
BGLM
3CVSRaison de mise en demeure RésultatExecuter
7GOMBERefus de remise en état Remise en état Par agent BE
8GOMBERefus de remise en état Remise en état Par agent BE
9GOMBERefus de remise en état Remise en état Par agent BE
43GOMBERefus de remise en état Remise en état Par agent BE
Operations
Cells with Data Validation
CellAllowCriteria
B4:B43List=Societes!$A$2:$A$6
G4:G43List=Societes!$B$2:$B$4
L4:L815List=Societes!$C$2:$C$5
M4:M815List=Societes!$D$2:$D$5
 
Upvote 0
Again, look at B9 on the Rapport sheet that you posted initially. It says DELVAUX which does not match any of your sample data. If I change that cell (B9) to GOMBE, then the formula in F9 calculates correctly.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,854
Messages
6,121,941
Members
449,056
Latest member
denissimo

Share this page

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