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

=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

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

John Wrote:
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

Hi John

Try this =IF(AND(A1="X",A2="X"),SUM((B1*C1)+(B2*C2)),"")


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475995

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

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

With the Sumproduct i was able to multiply and add the date across two rows
but not with 2 columns.....i tried that without giving the condition
A1:A2="X"....So i am not sure how to input a condition into the sumproduct
function....Guys i would be thankful if you can help me on this

"Roger Govier" wrote:

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

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

Hello paul
Thanks for your reply...Your formauls works really good...But i don't want
to assign each cell to the "X" text data separetly...I will be glad if i can
select a range and then assign "X"...I tried giving a range A1:A3="X" it gave
me #VALUE as the result....Any suggestions

"Paul Sheppard" wrote:


John Wrote:
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

Hi John

Try this =IF(AND(A1="X",A2="X"),SUM((B1*C1)+(B2*C2)),"")


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475995

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