Function for different array

[John]

Hi all,

I need help on writing formula for the problem below:

Column A Column B Column C
CHJ 15 AGB
AGB 10 HHH
JKX 12 NNN
MNB 19 MMM
AGB 20 BBB
. . .
. . .
. . .

I want to be able to write " If one or more value in Column A equal to one
of the value in Column C then Sum column B ". In this case the answer is " 30
", the sum of "AGB".

Thank you
John

 

[Jacob Skaria]

=SUMIF(A:A,"agb",B:B)

OR

=SUMIF(A:A,C1,B:B)

If this post helps click Yes

 

[Ms-Exl-Learner]

This Can be arrived in 2 simple ways.

=SUMIF($A$2:$A$6,$C2,$B$2:$B$6)

OR

=SUMPRODUCT(($A$2:$A$6=$C2)*($B$2:$B$6))

Change the Cell Reference A2:A6 to your desired cell in the above formula,
if required.

If this post helps, Click Yes!

 

[John]

Hi Jacob,

Thank you for the quick answer however this is what I want. I want be able
to write like:

SUMIF(A:A, C:C, B:B) - Meaning: Sum If one of the value in range A:A equal
to one of the value in range C:C then Sum B:B. So instead of C1 in your
formula, I want to be able to put the range C:C "=SUMIF(A:A,C1,B:B)"

Thank you
John

 

[John]

Thank you for the quick reply however I want to be able to put the range
instead of the reference cell only. In your formula
=SUMIF($A$2:$A$6,$C2,$B$2:$B$6) I want to be able to change C2 to $C2$:$C6$.

Thank you
John

 

[Jacob Skaria]

Try
=SUMPRODUCT(SUMIF(A:A,C1:C100,B:B))

If this post helps click Yes

 

[John]

Thank you, Jacob. It works.

Try
=SUMPRODUCT(SUMIF(A:A,C1:C100,B:B))

If this post helps click Yes