Hi,
I am trying to add for column C where column A has Ron and Column B has US. Can you help?
A B C
Ron US 5
ND India 2
ND US 5
ND US 6
Suji India 1
Ron Europe 5
Ron Europe 10
Ron US 16
Ron US 12
Thanks
Ron Coderre wrote:
You're very welcome.....I'm glad I could help.
28-Mar-08
You're very welcome.....I am glad I could help
Regards
Ro
Microsoft MVP (Excel)
Previous Posts In This Thread:
Re: Sumif with multiple columns in sum_range
Try this approach
=SUMPRODUCT((A2:A10="Joel")*B2:C10
Adjust range references to suit your situation
Is that something you can work with
Post back if you have more questions
-------------------------
Regards
Ro
Microsoft MVP (Excel
(XL2003, Win XP
You're very welcome....I'm glad I could help.
You're very welcome....I am glad I could help
Regards
Ro
Microsoft MVP (Excel
(XL2003, Win XP)
Re: Sumif with multiple columns in sum_range
I can never get my head round the SUMPRODUCT function. Can anyone explain i
very simple terms(!!!) what it does
Re: Sumif with multiple columns in sum_range
Bob Phillips explains =sumproduct() in much more detail here
http://www.xldynamic.com/source/xld.SUMPRODUCT.htm
gavin wrote
-
Dave Peterson
For a simple description, type the word SUMPRODUCT into Excel help.
For a simple description, type the word SUMPRODUCT into Excel help. Fo
more detail, type the words SUMPRODUCT and Excel into Google
-
David Biddulph
I see that Dave Peterson gave you the xldynamic link, which is
I see that Dave Peterson gave you the xldynamic link, which is ver
comprehensive
To augment that a bit, I'll describe the SUMPRODUCT functionalit
as it applies to formula in this thread
Let's see if this helps
SUMPRODUCT was *designed* to multiply two or more same-siz
ranges of cells and sum the products
Example (in cells A1:B4)
Sale Rat
100 0.1
200 0.1
300 0.2
The commission would be the sum of each Sale x Rate
+(100 x 0.10
+(200 x 0.15
+(300 x 0.20
In this instance, the total happens to be 100
The SUMPRODUCT representation of that could be
=SUMPRODUCT(A2:A4,B2:B4
But SUMPRODUCT has an alternate structure, too
=SUMPRODUCT((A2:A4)*(B2:B4)
(Sometimes the second one works where th
first one fails and vice versa
The function multiplies th
A2 x B2 calcs 10.....adds to the tota
A3 x B3 calcs 30.....adds to the tota
A4 x B4 calcs 60.....adds to the tota
Commission = 100 (10+30+60
********************************
Before continuing...Let's talk a bit about Boolean Values
That's a fancy term for True/False values
This formula
A1: =(5=5
returns TRUE....5 DOES equal
an
This formula
A2: =(5=2
returns FALSE....5 does NOT equal
As such, TRUE and FALSE...are treated as WORDS by Excel
B1: =A1....returns TRU
an
B2: =A2....returns FALS
BUT...When you apply an arithmetic operator (+, -, *, /
to a Boolean Value Excel converts TRUE to 1 and FALSE to 0
So..
B1: =A1*1.....returns 1, instead of TRUE
B2: =A2*1.....returns 0, instead of FALSE
Actually, multiplying by any number convert
TRUE to 1 and FALSE to 0
So...this would happen, too
B1: =A1*10.....returns 10
B2: =A2*10.....returns 0
*********************************
We'll exploit that feature in SUMPRODUCT....
In this formula
=SUMPRODUCT((A2:A10="Joel")*B2:C10
This expression
(A2:A10="Joel")....returns a series of TRUE/FALSE value
depending on whether the cell equals "Joel" or not
an
B2:C10 contains NUMBERS
SO.
Each TRUE when multiplied by its associated NUMBE
returns that number
Each FALSE when multiplied by its associated NUMBE
returns converts to a zero...0 x number = 0.
SUMPRODUCT returns the sum of all those results
which is the sum of all combinations where
Col_A="Joel"
Experiment with simple examples until you get
comfortable with the way it works.
I hope that helps.
Post back if you have more questions.
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Re: Sumif with multiple columns in sum_range
Oh no! I did not understand a word of that - and I thought I was reasonably
intelligent :-(
Sumif with multiple columns in sum_range
Hello,
I have a set of data that has a label in column A the sales in column
B, C & D:
Column A Column B Column C
Joel 500 677
Joel 575 752
Joel 650 827
Claudia 725 902
Tarzan 800 968
I'd like to run SUMIF where the "Range" is column A, the "Criteria" is
Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C)
I only get the sum of column B which is 1,725.
Can someone hook me up with a formula that can sum both column B & C
in this situation.
Thanks, in advance
Daniel
Re: Sumif with multiple columns in sum_range
=A0677
=A0752
=A0827
something something
Re: Sumif with multiple columns in sum_range
wrote:
Perfect, thank you very much!
Re: Sumif with multiple columns in sum_range
Hi Ron,
I wanted to thank you for your GREAT explanation:
My question is this, I'm using Excel97, does this function work in this
version?
My formula
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)
is not working. I thought I understood exactly what you were saying, maybe
not.....
--
TIA
AFJr
Hi, AF JrIf you're not getting a #NAME!
Hi, AF Jr
If you're not getting a #NAME! error,
then Excel 97 has the SUMPRODUCT
function.
There are 2 variations of the structure.
If this one doesn't work:
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)
Try this one:
=SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62)
Does that help?
Post back if you have more questions.
Oh, and thanks for the feedback on the explanation.
Much appreciated.
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Hi Ron,Thanks for your quick response.I'm getting a #VALUE!
Hi Ron,
Thanks for your quick response.
I'm getting a #VALUE! error.
Sorry for not posting that earlier. I'm getting this error using both
structures of the formula. I'm stumped, any ideas?
One more question, the "--" =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62)
what does that mean? I've tried it with and w/o that, still no luck.
--
TIA
AFJr
:
The Double-Unary (--) forces a conversion of TRUE/FALSE values to numbers.
The Double-Unary (--) forces a conversion of TRUE/FALSE values to numbers.
--TRUE = 1
--FALSE = 0
so...--{TRUE, TRUE, FALSE}...becomes {1,1,0}
You could also multiply boolean values to the same effect:
1*TRUE = 1
1*FALSE = 0
But, the Dbl-Minus indicates to knowledgable users that
a numeric conversion is intended, versus a calculation.
Regarding your #VALUE! error....scan the referenced data and see if
there are any errors or irregularities in it.
Does that help?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Hi Ron,Thanks for sticking with me through this.
Hi Ron,
Thanks for sticking with me through this. I've checked the data for errors.
I'm not sure if this is a problem or not.
The targeted cells in my formula:
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)
B21:B62 are just text selected from a list.
Q21:S62 contain formulas, columns Q & R formula is: =IF($E:$E=0,"",$E:$E*0)
{the zero that is multiplied could be a value or zero}
column S is: =IF($E:$E=0,"",(Q22+R22)*0.15)
(the data in column "E" is a number that represents a "Qty" multiplier)
So when the SUMPRODUCT function is executed it looks at column B to see if
the value ="C" and produces a "1" if it is(True). If it evaluates to "1",
columns Q:S formula's are executed and added together. Being that a zero
could result in the the formula's in columns Q:S I suspect this is giving me
my #VALUE! error. Multiplying any number by zero results in zero but, why
would this be a problem? Maybe its the way excel evaluates it. If so, I've
got to come up with another way to do this.
Does this make sense to you? Any other suggestions?
--
TIA
AFJr
:
Re: Sumif with multiple columns in sum_range
It's time to try another approach:
Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of ENTER):
=SUM(IF($B$21:$B$62="C",IF(ISNUMBER($Q$21:$S$62),$Q$21:$S$62)))
Does that help?
-------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Ron,You are a godsend!
Ron,
You are a godsend! Yes that worked beautifully. I was ready to move on and
try something else but really had no idea.
Again, I really appreciate you sticking with me, trying different things and
such. Helping me to open my mind. I love user groups, a place to give help
and get help.
Thanks again Ron,
Have a great weekend!
AFJr
:
You're very welcome.....I'm glad I could help.
You're very welcome.....I am glad I could help.
Regards,
Ron
Microsoft MVP (Excel)
Submitted via EggHeadCafe - Software Developer Portal of Choice
Geocoding / Address Validation with MSN Virtual Earth
http://www.eggheadcafe.com/tutorial...8f-80fa2d6938a5/geocoding--address-valid.aspx