Complicated SUMIF Case

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 
John said:
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
Click to expand...

Hi John

Try this > =IF(AND(A1="X",A2="X"),SUM((B1*C1)+(B2*C2)),"")
 
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
 
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)
 
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
 
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))
 
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
 
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
 
Back
Top