Excel 2007 : Vlookup and sum similar data

[Ichigo]

Dear members,

I have a question on vlookup. I was following some previous thread, this link being one of them:
http://www.excelforum.com/excel-gene...not-cells.html

I have attached a sample of my question here. What if my data has similar region code but different product code. I want to sum up all data with code N1010 and 20050. My intended answer is 95 but it seems my formula is not accurate.

Hope to get some help here.

Thank you.

[teylyn]

Hi

try

=SUMPRODUCT(--(A5:A14="N1010"),--(B5:B14=20050),C5:C14)

or, better, create a privot table from your data to analyse and sum up by different criteria. There's lots of information about pivot tables at www.contextures.com - once you get your head around the concept you wouldn't want to miss them!

hth

[Ichigo]

Thank you Teylyn,

Can i ask what does the "--" symbol means in this formula? It seems if i delete one of it, the answer will become negative.

Thanks again.

[teylyn]

It's called a double unary and converts TRUE or FALSE into 1 or 0 respectively.

(A5:A14="N1010") in the formula will return something like

{TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}

with the -- in front, it will convert to

{1,1,1,1,0,0,0}

which can then be used for calculations, wherease TRUE or FALSE can not.

[Ichigo]

Hi Teylyn,

Thank you for the explanation.