Sum Product maybe??

[Mark Allen]

I have the following spread sheet that I need to calculate the value of if "Y"

A B C D
Sold Product Value Date
Y Hardware $20.00 Jan-09
Y Software $50.00 Feb-09
N Hardware $50.00 Jan-09

Ok, so I want to know the "value" of the Hardware items sold "Y" in Jan-09.

Do I use SUMPRODUCT

Hope some one can help please.

Regards

Mark

 

[galimi]

Mark,

Use the following formula

=SUM(IF(D911&A9:A11=D11&A9,C9:C11,0))

and press cntrl+shift+enter instead of just enter. This assumes your data
starts in cell A8 (sold)

 

[Pete_UK]

Try this:

=SUMPRODUCT((A2:A100="Y")*(TEXT(D2100,"mmm-yy")="Jan-09"),C2:C100)

Adjust the ranges to suit, but you can't use full-column references
before XL2007.

Hope this helps.

Pete

 

[Dave Peterson]

I'm guessing that the values in column D are really dates formatted as
mmm-yy--not plain old text.

=sumproduct(--(a1:a99="y"),
--(b1:b99="hardware"),
--(text(d1:d99,"yyyymm")="200901"),
(c1:c99))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html