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

Mark,

Use the following formula

=SUM(IF(D9:D11&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)
--
http://www.ExcelHelp.us

Call me at
1-888-MY-ETHER ext. 01781474



"Mark Allen" wrote:

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

Try this:

=SUMPRODUCT((A2:A100="Y")*(TEXT(D2:D100,"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

On Nov 28, 3:43*pm, Mark Allen
wrote:
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

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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



Mark Allen wrote:

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

--

Dave Peterson