adding up limited items, formula needed

[Dave W]

I am showing a partial table below that I need help with
a procedure or formula.

col a col b col c col d col e col f col h col i
CatName Item price Qty cost MU profit total

L.04_A.. labor.. 18.00 18 324 20% 32.00 420.8
M.01_S.. mel 3/4 .72 32 23.04 30% 2.90 36.8
L.08_I.. install.. 42 1 42 10% 4.2 4.6
T_Re.. Tax .05 enter #value 0% #value


col a -items from a drop down list through data validation

col b- items from a drop list indirect data validation

col c- info from a vlookup from col b

col d- input manually

col e- simply calculation between col c & d

col f- info from vlookup from col a

col h- simple calculation with an ifcondition

col i -simple calculation between columns

I would like to extract certain info by using col a
categories so I can add up those as a group
I want to keep from selecting any category that
has L.08_Installation in its description.

Thanks in advance

 

[John Michl]

Not exactly sure what you mean by extracting but if you'd like to do
something like total up all of the
=SUMIF(col a,"<>L.08",col i) where the col references reference the data in
the column. I would suggest adding some additional formulas to avoid #value
errors and possibly replace them with zeros. Also, besure that you use the
"False" argument in your lookups to ensure that the lookup gives the correct
value if it doesn't find the item it is looking up. I usually use something
like the following:

if(iserror(vlookup(A1, B2:C100, 2, false)),0,vlookup(A1,B2:C100,2,false))

John
www.JohnMichl.com

 

[Dave W]

Thank you John

this has sent me in the right direction