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?

 

[XLENT]

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.

 

[jlkirk]

xlent-did the search-no help. Any other ideas?

 

[XLENT]

For example...

http://www.mrexcel.com/board2/viewtopic.php?
p=223294&highlight=conditional+sum#223294

On the search use something like CONDITIONAL AND SUM

 

[Aladin Akyurek]

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?

=SUMPRODUCT(($A$2:$A$10 < E1)+0,$b$2:$B$100=F1)+0,$C$2:$C$100)

where E1 houses 6/1/03 and F1 an inventory piece of interest.

An alternative is using a DSUM formula which you can find on this site in a few threads associated with my name.

 

[jlkirk]

xlent-looked at that message/topic-no help. Used conditional sum as you suggested and it returen a "Value".

 

[eliW]

Hi,

If you wouldn't insist on 1/7 it could have been done easily with PIVOT TABLE with grouping DATES to Years or Quarters

If you need more details, just ask!

Eli

 

[jlkirk]

Thanks Aladin. Works fine. How about two more questions: what if I want to (a) sum up the costs of 2 or more pieces of inventory that were (b) acquired, say, between November 1, 2002 and April 15, 2003?

 

[jlkirk]

Aladin,
Thanks again. In trying to learn more about this function, I am puzzled as to what the significance of the "+0" is in the first two expressions?
Thanks,
Jeff

 

[Aladin Akyurek]

Aladin,
Thanks again. In trying to learn more about this function, I am puzzled as to what the significance of the "+0" is in the first two expressions?
Thanks,
Jeff

+0 does effect coercion, that is, conversion from logical to number... Other arithmetic operators, -, *, have the same effect as well as --.

=TRUE+0 --> 1

=FALSE+0 --> 0

or conversion from numbers stored as text to real numbers, e.g.,...

="007"+0 --> 7

="00000"+0 --> 0

Formulas operating computed arrays like ones with SumProduct needs that coercion in order to work.

For more, see...

http://www.mrexcel.com/wwwboard/messages/8961.html