Sumifs is giving me 0 even though it clearly is >0

[xaikus506]

Hi I have a problem, I don't know what happen sometimes it gives me a correct value and sometimes 0 in the number of error values

Dashboard.xlsx
	A	B	C	D	E
6	Name of Analyst	Date of Process	Number of Errors	Subtask	Approver's Name
7	Azuelo, Chad	01/11/2022	2	CoReq: 22-01-10	Mendoza, Leslie
Dashboard Prot

Cell Formulas
Range	Formula
A6:E7	A6	=VSTACK({"Name of Analyst","Date of Process","Number of Errors","Subtask","Approver's Name"}, IFERROR(IF($B$2="",{"","","","",""}, IF(OR($B$2="all",$B$2="All",$B$2="ALL"), CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:H, (ISERROR(SEARCH("Analyst*",'Error Logs Consolidated'!D:D))* ('Error Logs Consolidated'!D:D<>0))* (ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))),2,1),3,1),{3,2,5,4,1}), IF(AND($B$2<>"",$B$3="Name of Analyst"), CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:H, (ISERROR(SEARCH("Analyst",'Error Logs Consolidated'!D:D))*(ISNUMBER(SEARCH($B$2,'Error Logs Consolidated'!D:D)* (ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B))))))),2,1),3,1),{3,2,5,4,1}), IF(AND($B$2<>"",$B$3="Date of Process"), CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:I, (ISNUMBER(SEARCH($B$2,'Error Logs Consolidated'!I:I)* (ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))))),2,1),3,1),{3,2,5,4,1}), IF(AND($B$2<>"",$B$3="Subtasks"), CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:H, (ISNUMBER(SEARCH($B$2,'Error Logs Consolidated'!E:E)* (ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))))),2,1),3,1),{3,2,5,4,1}),"") )))),{"","","","",""}))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E1,A4:E1048576,A2:A3,C2:E3	Cell	does not contain a blank value	text	NO

Dashboard.xlsx
	G	H	I
6	Name of Analyst	Subtask	Number of Errors
7	Azuelo, Chad	CoReq: 22-01-10	0
Dashboard Prot

Cell Formulas
Range	Formula
G7:H7	G7	=UNIQUE(CHOOSECOLS(FILTER(A:D,(A:A<>"")*(A:A<>A2)*(A:A<>A3)*(A:A<>A6)),{1,4}))
I7	I7	=SUMIFS(C:C,A:A,BYROW(G7:INDEX(G:G,COUNTA(G:G)+5),LAMBDA(x,x)),D:D,BYROW(H7:INDEX(H:H,COUNTA(H:H)+5),LAMBDA(y,y)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Dashboard Prot'!_FilterDatabase	='Dashboard Prot'!$A$6:$D$301	I7, G7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1:I3,G6:I1048576,G4:H5	Cell	does not contain a blank value	text	NO

 

[bebo021999]

Sometime formula gives wrong result, due to some reasons.
One of them is "Circular References"
First check if any cell in sheet had refered to itself.

 

[StephenCrump]

C7 looks like "2" (text) rather than 2 (numeric).

If your values in column C are text, then your SUMIFS() in column I will return 0.

 

[xaikus506]

C7 looks like "2" (text) rather than 2 (numeric).

If your values in column C are text, then your SUMIFS() in column I will return 0.

Hi Stephen, Thanks for the response. I am not sure if I did change the data type in Cell C correctly to number but it still does not work.

but this works correctly as intended

 

[RoryA]

With the data in your first picture, select C7 and click the Left align button on the ribbon to remove any manual alignment. Does the number stay on the left? If so, it's text, not a number, and you need to look at the source data.

 

[xaikus506]

Hi @RoryA, Thanks for the response. I managed to find the culprit of the error. the cell in the raw data was in text format. Thank you for your assistance.