You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
That did it. Thank you all very much.

"T. Valko" wrote:

Did you enter the formula as an array?

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula
you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT
key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formulas in squiggly brackets
{ }.
You can't just type these brackets in, you *must* use the key combo.
Also,
anytime you edit an array formula it *must* be re-entered as an array
using
the key combo.

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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