Hi John

You are missing the double unary minuses that Bob posted in his solution
to you.
They are use to coerce the True or False result from B4:B5="X" into 1's
and 0's.

=SUMPRODUCT(--(B4:B5="X"),(C4:C5*D4:D5))



Regards

Roger Govier



John wrote:

Bob I don't know what's going on with my excel but i couldn't fix that thing
yet.....I am just trying with 2 rows and 2 columns ....Have no idea why that
isn't working...I am pasting the syntax i am using ....Can you tell me if it
is right
=SUMPRODUCT((B4:B5="X"),(C4:C5*D4:D5))

"Bob Phillips" wrote:



And so says Bob too <vbg

Seriously John, I did test it so as Roger says, it is probably data
problems. Cut the data down to a few lines and test it, increasing it bit by
bit until you get an error. That will point you closer to the error.

Bob
(remove nothere from email address if mailing direct)

"Roger Govier" wrote in message
.. .


Hi John

Bob's formula is correct and works fine. It returns 110 for the two rows
of data you show.
There must be something wrong with your data. Numbers entered as Text?
Spaces before or after entries?

Regards

Roger Govier



John wrote:



Thanks for your reply Bob....But it was not working ....It was returning


a


#value ....Can you please try with some examples and see why it was wrong

"Bob Phillips" wrote:





=sumproduct(--(A1:A4="X"),B1:B4*c1:C4)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...




I have a question with IF and SUMIF stuff... In my situation i have


Text




data




in Column from A1:A4....Have Numerical data in Column from B1:B4 and


from


C1:C4....then i need a formula in D1(Any single cell)which will do this




i.e.




" If in column A1:A4 Is "X" then Multiply the Corresponding cells in
(B1:B4)*(C1:C4) and sum the total of each single row at our D1.

For Ex:
1st Row = Cell A1 = "X" ; Cell B1 = 3 ; Cell C1 = 20
2nd Row = Cell A2: "X" ; Cell B2 = 5 ; Cell C2 = 10

Then in a single cell we should have a condition which will check to


see




if




we have "X" if that is true then it will multiply the corresponding B1


*




C1




In our Case at Cell D1 = If( A1:A2 = "X" then do (B1*C1) + ( B2*C2)

It's been a while that i am fighting with this...I will be thankful if
someone can help me with this