sum with multi criteria

A

azoz

I Have 3 columns with like 800 rows
column #1 is the year , column #2 is Codenumder , column #3 is the cost

year code cost
#1 #2 #3
2003 80 500
2001 10 100
2003 230 1000
2002 80 50
2006 10 1500
2003 80 1000

i need to get the cost of the year 2003 that has a codenumber 80
wich here if i do it with my eyes its 1500
how can i make a function for it , to do it for me , even a macro if needed
 
B

Bob Phillips

=SUMPRODUCT(--(A2:A200=2003),--(B2:B200=80),C2:C200)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
A

azoz

excel says there is an error in formula
i did it even with ctrl+shift+enter "wich never works for me"
=========
 
B

Bob Phillips

It works fine, and doesn't need to be array entered. Perhaps you have errors
in the data being evaluated.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
M

Mike Fogleman

Re-check your formula. Bob's formula is correct and works for me. Copy &
paste from your news reader if necessary. That is how I did it.
Mike F
 
A

azoz

ok it worked on my office 2002 at home
but in work i have excel 2003 and it wont work there
==========
 
D

Debra Dalgleish

Maybe your work computer is set up to use semi-colons in formulas,
instead of commas:

=SUMPRODUCT(--(A2:A200=2003);--(B2:B200=80);C2:C200)
 
B

Bob Phillips

In what way did it not work?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 

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