SUMIFS with Multiple Criteria

[AllisterB]

I and trying to get the total of values in a table that belong to Dept A and have Codes after 9990.000.
the Result I get is 0 but it should be 50.

What is wrong with my SUMIFS formula?

Thank You

Book1
	G	H	I	J	K
4
5		Code	Dept	Value
6		9910.100	A	10
7		9920.200	B	20
8		9940.001	B	30
9		9990.000	C	40
10		9991.000	A	50
11
12
13		0
Sheet1

Cell Formulas
Range	Formula
H13	H13	=SUMIFS(Table1[Value],Table1[Dept],"A",Table1[Code],">9990.000")

 

[mrshl9898]

Working here. Just checking it is table1?

Also see if this is TRUE

=ISNUMBER([@Code])

 

[Eric W]

My bet is that your H6:H10 range is text, so the codes are numbers saved as text, and the SUMIFS is looking for numeric values. You can try something like this:

=SUMPRODUCT(Table1[Value],--(Table1[Dept]="A"),--(Table1[Code]+0>9990))

 

[AllisterB]

the table is Table1 and the ISNUMBER result is False

 

[mrshl9898]

As per Eric then

 

[AllisterB]

All Done - Thanks Eric

 

[Eric W]

Glad we could help!

 

[Tetra201]

My bet is that your H6:H10 range is text, so the codes are numbers saved as text, and the SUMIFS is looking for numeric values...

It looks like this can be circumvented by inserting an apostrophe in front of the 9990.000:

=SUMIFS(Table1[Value],Table1[Dept],"A",Table1[Code],">'9990.000")

 

[AllisterB]

Nice Tidy solution - thank you