calculate sum of the product from one of the column

**top1** · 09-05-2012, 10:04 PM

Hi,

I was trying to track my inventory and sale of the product. Please see the attached file.
Please let me know if you have question to understand my question.

ThanksBook1.xls Book1.xls

**vlady** · 09-05-2012, 10:29 PM

try this one.

Hamsample.xls

**top1** · 09-06-2012, 12:24 AM

Thanks Vlady. What if I want to use generic word instead a specific one in the formula. Like Intead of writing Ham&Cheese, I can get the result just writing HAM.

**vlady** · 09-06-2012, 01:12 AM

You can use wild cards for it

INDEX(F:F,MATCH("*"&P14&"*",A:A,0))

i put it before and after so that either of the word will be searched

Ham or Cheese only

**top1** · 09-06-2012, 06:05 PM

Hi Vlady,

I tried your suggestion but not work out. I put word Ham instead of HAM&Cheese, but not worked. Can you please help on this.

**vlady** · 09-06-2012, 07:12 PM

here's the file

Hamsample.xls

**top1** · 09-06-2012, 09:51 PM

Hi Vlady,

Please if you can explain the last answer file. I am not getting any idea.

Thanks

**vlady** · 09-06-2012, 10:06 PM

=IFERROR(SUM(INDEX(C:C,MATCH("*"&P14&"*",A:A,0)),INDEX(F:F,MATCH("*"&P14&"*",A:A,0))),"Not found")

P14 is the text to find say the word --> cheese

notice the "*" between P14 so even you type cheese this will be search even there are "other words" between it and search it... notice the additional word below

i.e ->> BurgerHam&cheesehotdog <-- this will be the returning value in the match because it has the word cheese in it..

then INDEX(C:C <--get the quantity

iferror( ..the formula here.....,"Not found") <--used for error checking if not available

**top1** · 09-06-2012, 11:49 PM

Hi Vlady,

how can I get sum of QTY if I have like 7 QTY columns instead of just 2 QTY columns.

thanks

**vlady** · 09-06-2012, 11:12 PM

btw iferror work for higher xl

this for xl2003
=IF(ISERROR(MATCH("*"&P14&"*",A:A,0)),"not found",SUM(INDEX(C:C,MATCH("*"&P14&"*",A:A,0)),INDEX(F:F,MATCH("*"&P14&"*",A:A,0))))

thanks icestationzbra.

regards,
vladimir