Sumif/Countif/Vlookup (Not sure which one, if any)

[jlkirk]

I have a spreadsheet that lists inventory parts, the date they were purchased and their cost in each of three columns (column A the date they were purchased; column B, the name of the particular part (there are many, but some are repeats); and column C the cost). What I would like to do is create a formula that would add all of the costs of a particular inventory piece acquitred before June 1 of a given year.
Any help?

 

[jlkirk]

Thanks Aladin. Did you happen to have an opportunity to think about my two additional questions?

 

[jlkirk]

Look at CONDITIONAL SUM WIZARD (this is an add in) - also do a search as there are countless examples of this using SUMPRODUCT and other array formulas...

Tip : Use Aladin as author.

Xlent,
I have been toying around with the CONDITIONAL SUM WIZARD at your suggestion. One question, Assume I have > 10 discreet inventory parts, but > 50 in the total number of parts in my inventory. Can I use this tool to find, say, the total cost of 4 types of inventory acquired before a certain date? For example, assume my inventory consists of various hand tools (hammer, screwdriver, file, saw, etc.-each name being a discreet name of a piece of inventory). Assume further I have several hammers, screwdrivers, files saws, etc., and I want to total the cost of all of the hammers and saws acquired before May 1, 2003. Can I use the conditional sum to do this?
Thanks,
Jeff

 

[XLENT]

I guess you're saying you want to use a conditional sum with an if(or( and an and

i.e {sumif(if or(screwdriver, hammer) and (date<....) ,sum....)}

whereas the default for the conditional sum wizard is an IF AND (no OR)... so no I don't think you can - you would need so set up a separate conditional sum for each type (hammer, screwdriver) etc... and then calculate the sum of all the conditional sums.

Aladin maybe able to conjure up some magic though...

EDIT - I think you're also getting at the point as to whether or not it would be able to ascertain a type by part of the string - ie establish that somethings a screwdriver because it's called Remmington Steele Screwdriver... again, no. You would need to establish a grouping by type.

You could do this in VBA quite easily but with formulas it gets a little more tricky than I am capable of - again, over to Aladin.

 

[Aladin Akyurek]

Thanks Aladin. Did you happen to have an opportunity to think about my two additional questions?

Could you post 10 rows of the relevant data along with criteria and desired results?