Countifs with more criteria returns 0

[LejokeR]

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
	B	C	D	E	F	G	H	I
1	RAPPORT DES OPERATIONS DE CONTROLE: BLUE ENERGY - SNEL
2							Statistique en %
3							Remise en état	10%
4							Deconnecter	56%
5							Installer	76%
6
7	CVS	REFUS	Nbr	ACTIONS	NOMBRE DE COMPTEURS INSTALLER PAR
8					Par agent BE	Par agent SNEL	Par agents BE et SNEL	SNEL / Menaces
9	DELVAUX	Refus de remise en état	18	Remise en état	0
10
11				Deconnecter	0
12
13		Refus d'accès au contrôle	22	Controler
14				Remise en état	0
15				Deconnecter	0
16		Refus d'installation	93	Installer	0
17				Deconnecter	0
Rapport

Cell Formulas
Range	Formula
F9	F9	=COUNTIFS(Operations!B4:B815,$B$9,Operations!G4:G815,$C$9,Operations!M4:M815,$F$8,Operations!L4:L815,$E$9)
F11	F11	=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")
D9	D9	=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,Rapport!C9)
D13	D13	=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,"=Refus d'accès au contrôle")
D16	D16	=COUNTIFS(Operations!B4:B815,Rapport!B9,Operations!G4:G815,"=Refus d'installation")
F14:F17	F14	=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
Cell	Condition		Cell Format	Stop If True
I3:I5	Other Type	DataBar		NO
H3	Other Type	Icon set		NO

Cells with Data Validation
Cell	Allow	Criteria
B9:B17	List	=Societes!$A$2:$A$6
F8	List	=Societes!$D$2:$D$5

 

[LejokeR]

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.

I'd like each changement of CVS in dashboard then i have differents results from coming to operation table.

 

[RoryA]

Yes, and that should work with the formula you have, assuming there actually is matching data. It does for me.

 

[LejokeR]

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.

In operation table, i filtered GOMBE and shared here. It's a sample.

 

[LejokeR]

Yes, and that should work with the formula you have, assuming there actually is matching data. It does for me.

Maybe issue of office version. Because it don't work.

 

[LejokeR]

Maybe issue of office version. Because it don't work.

Maybe issue of office version. Because it don't work.

How share you my file for analyzing?

 

[RoryA]

I am using 2016 just like you are.

Put the file on a sharing site like Onedrive or Dropbox and then post a link here. Make sure the link allows public access.

 

[LejokeR]

Okay! Let me to do it.

I am using 2016 just like you are.

Put the file on a sharing site like Onedrive or Dropbox and then post a link here. Make sure the link allows public access.

Okay!
Let me to do it.

 

[LejokeR]

I am using 2016 just like you are.

Put the file on a sharing site like Onedrive or Dropbox and then post a link here. Make sure the link allows public access.

Report_Observation_Ajours.xlsx

Microsoft Excel Workbook

1drv.ms

 

[RoryA]

The formula in your file is not the one you posted originally. That original formula does work but E9 actually should have a space on the end of it, because the values on the Operations sheet have the trailing space too.

 

[LejokeR]

The formula in your file is not the one you posted originally. That original formula does work but E9 actually should have a space on the end of it, because the values on the Operations sheet have the trailing space too.

It's because i'm working on it while i'm sending the file. Please share me formule correct related agent BE for remise en etat and Deconnecter. I want to continue with rest