Dear Dave/Excel experts,

I have errors doing SUMIFS function.

Below are my tables:

Category Code Amount

ANA 100.01 $ 50
LPP 100.05 $100
100.03 $ 50
ANA 100.01 $ 200
LPP 100.05 $ 100
100.03 $ 50
ANA 100.01 $ 200
LPP 100.05 $ 100

Table CATEGORY and CODE are formated as TEXT
Table AMOUNT is formated as number

I used SUMIFS using 2 criterias on the :

1. RANGE = Table Category Criteria = LPP
2. Range = Table Code Criteria = 100.01 (all that has 100.01)
SUM Range = Table AMOUNT

VALUE is error.

Other Test Scenarios:

1. If, I use SUMIFS using ONLY criterea #1 OR use SUMIF function, the
result is good. The problem is with criterea #2.

2. If I use SUMIF with criteria #2, the value is good

Help on this please?

Thanks a bunch

This worked ok for me:

=SUMIFS(C:C,A:A,"ana",B:B,"100.01")

If it doesn't work ok for you, check to see if you have errors in any of those
ranges -- maybe in hidden rows???



apache007 wrote:

Dear Dave/Excel experts,

I have errors doing SUMIFS function.

Below are my tables:

Category Code Amount

ANA 100.01 $ 50
LPP 100.05 $100
100.03 $ 50
ANA 100.01 $ 200
LPP 100.05 $ 100
100.03 $ 50
ANA 100.01 $ 200
LPP 100.05 $ 100

Table CATEGORY and CODE are formated as TEXT
Table AMOUNT is formated as number

I used SUMIFS using 2 criterias on the :

1. RANGE = Table Category Criteria = LPP
2. Range = Table Code Criteria = 100.01 (all that has 100.01)
SUM Range = Table AMOUNT

VALUE is error.

Other Test Scenarios:

1. If, I use SUMIFS using ONLY criterea #1 OR use SUMIF function, the
result is good. The problem is with criterea #2.

2. If I use SUMIF with criteria #2, the value is good

Help on this please?

Thanks a bunch

--

Dave Peterson

Dave,

Thanks for the correction. It works now.


"Dave Peterson" wrote:

This worked ok for me:

=SUMIFS(C:C,A:A,"ana",B:B,"100.01")

If it doesn't work ok for you, check to see if you have errors in any of those
ranges -- maybe in hidden rows???



apache007 wrote:

Dear Dave/Excel experts,

I have errors doing SUMIFS function.

Below are my tables:

Category Code Amount

ANA 100.01 $ 50
LPP 100.05 $100
100.03 $ 50
ANA 100.01 $ 200
LPP 100.05 $ 100
100.03 $ 50
ANA 100.01 $ 200
LPP 100.05 $ 100

Table CATEGORY and CODE are formated as TEXT
Table AMOUNT is formated as number

I used SUMIFS using 2 criterias on the :

1. RANGE = Table Category Criteria = LPP
2. Range = Table Code Criteria = 100.01 (all that has 100.01)
SUM Range = Table AMOUNT

VALUE is error.

Other Test Scenarios:

1. If, I use SUMIFS using ONLY criterea #1 OR use SUMIF function, the
result is good. The problem is with criterea #2.

2. If I use SUMIF with criteria #2, the value is good

Help on this please?

Thanks a bunch

--

Dave Peterson