conditional sum

P

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
 
P

Per Jessen

Hi

Try this formula:

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

Regards,
Per
 
S

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
 
P

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
 
P

Picman

This didn't work for me.

Shane Devenshire said:
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

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)
 
S

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
 
P

Picman

i'm using 2003, any ideas for me.

Shane Devenshire said:
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
 
F

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.
 
P

Picman

I'm still getting an error

franciz said:
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.
 
P

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

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.
 
X

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
 
P

Picman

That did it. Thank you all very much.

T. Valko said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top