conditional sum

[Picman]

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

 

[porter444]

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


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


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

ROCK ON!,

Scott

 

[Per Jessen]

Hi

Try this formula:

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

Regards,
Per

 

[Shane Devenshire]

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]

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

 

[Picman]

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

 

[Picman]

This didn't work for me.

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

 

[T. Valko]

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)

 

[Shane Devenshire]

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]

i'm using 2003, any ideas for me.

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

 

[franciz]

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]

I'm still getting an error

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]

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.

 

[T. Valko]

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.

 

[xlmate]

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]

That did it. Thank you all very much.

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!