Sum a range based on another range of cells

[Raj]

I want to sum a range based on another range of cells. I can detail my
problem as follows:

Range: 1
Name Points
A 1
B 2
C 3
D 4
E 5

Range: 2

Name
B
C
E


My intention is to sum the corresponding values of Range: 2 in Range: 1.

In another words to sum the values corresponding to B, C and E which would
be 10.

The main problem is the second range i.e. Range: 2 may change. I want to
have a solution for this.

Please send to my e-mail ID : (e-mail address removed) if you can solve this.
Please, please and please help me.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Jacob Skaria]

Hi Raj

Try the below formula

A2:B10 is your data
F2:F4 is the range 2

=SUMPRODUCT(SUMIF(A2:A10,F2:F4,B2:B10))

If this post helps click Yes

 

[Raj]

Hi Jacob Skaria,

Thanks, Dude.

It really works. My problem is resolved with your answer.

But one thing I didn't know is that how did you solve this. I cannot get
the formula logic. I thought "Sumif" can be given only for one criteria.
And "Sumproduct" can be used only for multiplication of a range of values to
another range of values. But your answer was simply amazing.

I would be grateful to you for the solution.

And one more request to you, can you please tell me about its working logic
i.e. how does it work with combination of Sumproduct with Sumif. (atleast in
this case)

Hoping that you will reply me with the way it works.

Thankfully,

Raj

 

[Jacob Skaria]

--The below formula would return a single result
=SUMIF(A2:A10,F2,B2:B10)

--The below would return a array of values for each value in F1:F3. In this
case 3 results
=SUMIF(A2:A10,F1:F3,B2:B10)

--SUMPRODUCT() adds up the arrray results for you..

If this post helps click Yes

 

[Jacob Skaria]

Hi Raj

The below formula would return 1 result
=SUMIF(A2:A10,F2,B2:B10))

The below would return an array of results. In this case 3 results. Array
enter the below formula selecting 3 cells (if the range is F2:F4)
=SUMIF(A2:A10,F2:F4,B2:B10)

'And SUMPRODUCT does the rest..which is to sum the results.
=SUMPRODUCT(SUMIF(A2:A10,F2:F4,B2:B10))

If this post helps click Yes

 

[Jacob Skaria]

Hi Raj

The below formula would return 1 result
=SUMIF(A2:A10,F2,B2:B10))

The below would return an array of results. In this case 3 results. Array
enter the below formula selecting 3 cells (if the range is F2:F4)
=SUMIF(A2:A10,F2:F4,B2:B10)

'And SUMPRODUCT does the rest..which is to sum the results.
=SUMPRODUCT(SUMIF(A2:A10,F2:F4,B2:B10))


If this post helps click Yes

 

[Jacob Skaria]

Oops...Really sorry about those multi-posts..it returned an error in the
first two instances...

 

[Raj]

Hi Jacob Skaria,

Thanks once again for the clarification of my doubt.

Earlier I was not having any logic/working of the formula you provided to
me, even though it was working fine. And hence I was eager to know the way
it works. But I couldn’t login to see your post because of some technical
issues, that’s why the reply is this much late and I feel sorry for that.

Please don’t feel sorry for those multi-posts, it’s OK.

My doubt is clarified with your answer. Thanks once again.

Thankfully,

Raj