After some testing the problem seems to be in the range. if i use only a
single cell reference (A2) i get the desired result, and when i input the
range again (A2:A16) i get an error.

"Picman" wrote:

I'm still getting an error

"franciz" wrote:

SUMIFS is a new function add to Excel 2007 version, you don't find it in
Excel 2003
In your case, use the

=SUM(IF(A2:A16="USD",OFFSET(A2:A16,1,2))) provided

pls click yes if this help.

"Picman" wrote:

i'm using 2003, any ideas for me.

"Shane Devenshire" wrote:

Hi,

I copied your data from the post and it worked fine for me. But you must be
using Excel 2007, this won't work in 2003.

Cheers,
Shane

"Picman" wrote:

This didn't work for me.

"Shane Devenshire" wrote:

Hi,

In 2007 you could use the following:

=SUMIFS(C2:C20,A2:A20,"USD",B2:B20,"Sales")

as well as the SUMPRODUCT formula from the previous post

If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Picman" wrote:

I have 3 columns of data, the first designates the currency of sales, the
second identifies the type of value input and the third is the value. I would
like add the total of only the "sales" in column (C) that have a cell 2
columns to the left (A - Currency) that contains a specific value (USD).

A B C
1 Currency Type Value
2 USD Sales $374.40
3 Freight $0.00
4 Freight % 0.00%
5
6 USD Sales $0.00
7 Freight $0.00
8 Freight % 0.00%
9
10 CAD Sales $2,635.14
11 Freight $63.13
12 Freight % 2.40%
13
14 USD Sales $0.00
15 Freight $0.00
16 Freight % 0.00%
17
18 CAD Sales $31,084.11
19 Freight $2,366.53
20 Freight % 7.61%


The total in this case would be $374.40