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

Try this:
=SUMPRODUCT(--(A3:A21="USD"), --(B3:B21="Sales"),--(C3:C21))


To learn more about SUMPRODUCT, check out this page:
http://www.contextures.com/xlFunctio...tml#SumProduct


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott

Hi

Try this formula:

=SUMPRODUCT(--(A2:A100="USD"),--(B2:B100="Sales"),C2:C100)

Regards,
Per

"Picman" skrev i meddelelsen
...
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

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

That worked fine. Thanks. My problem is now i need to total the freight from
the USD accounts only as well. I'm thinking some sort of offset?


My problem now is that i want to total the feight based on the same criteria


"porter444" wrote:

Try this:
=SUMPRODUCT(--(A3:A21="USD"), --(B3:B21="Sales"),--(C3:C21))


To learn more about SUMPRODUCT, check out this page:
http://www.contextures.com/xlFunctio...tml#SumProduct


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott

Sorry i got distracted and stated the same thing twice in the prievious post.

"Picman" wrote:

That worked fine. Thanks. My problem is now i need to total the freight from
the USD accounts only as well. I'm thinking some sort of offset?


My problem now is that i want to total the feight based on the same criteria


"porter444" wrote:

Try this:
=SUMPRODUCT(--(A3:A21="USD"), --(B3:B21="Sales"),--(C3:C21))


To learn more about SUMPRODUCT, check out this page:
http://www.contextures.com/xlFunctio...tml#SumProduct


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott

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

i need to total the freight from the USD accounts only as well

All USD accounts or just those that are type "sales" ?

As long as the location of "freight" has a consistent offset, 1 row below, 2
columns to the right.

Array entered** :

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
That worked fine. Thanks. My problem is now i need to total the freight
from
the USD accounts only as well. I'm thinking some sort of offset?


My problem now is that i want to total the feight based on the same
criteria


"porter444" wrote:

Try this:
=SUMPRODUCT(--(A3:A21="USD"), --(B3:B21="Sales"),--(C3:C21))


To learn more about SUMPRODUCT, check out this page:
http://www.contextures.com/xlFunctio...tml#SumProduct


--
If my posting was helpful, please click the "Yes" button.

ROCK ON!,

Scott

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

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

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

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

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

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

This is array formula, press Ctl,Shift and Enter on your keyboard all at the
same time. Post back if you don't get it

Hope this help. Pls click Yes if this help

cheers

"Picman" wrote:

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

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

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