Function for different array
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 |
Function for different array
=SUMIF(A:A,"agb",B:B)
OR =SUMIF(A:A,C1,B:B) If this post helps click Yes --------------- Jacob Skaria "John" wrote: 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 |
Function for different array
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! -------------------- (Ms-Exl-Learner) -------------------- "John" wrote: 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 |
Function for different array
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 "Jacob Skaria" wrote: =SUMIF(A:A,"agb",B:B) OR =SUMIF(A:A,C1,B:B) If this post helps click Yes --------------- Jacob Skaria "John" wrote: 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 |
Function for different array
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 "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "John" wrote: 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 |
Function for different array
Try
=SUMPRODUCT(SUMIF(A:A,C1:C100,B:B)) If this post helps click Yes --------------- Jacob Skaria "John" wrote: 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 "Jacob Skaria" wrote: =SUMIF(A:A,"agb",B:B) OR =SUMIF(A:A,C1,B:B) If this post helps click Yes --------------- Jacob Skaria "John" wrote: 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 |
Function for different array
Thank you, Jacob. It works.
"Jacob Skaria" wrote: Try =SUMPRODUCT(SUMIF(A:A,C1:C100,B:B)) If this post helps click Yes --------------- Jacob Skaria "John" wrote: 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 "Jacob Skaria" wrote: =SUMIF(A:A,"agb",B:B) OR =SUMIF(A:A,C1,B:B) If this post helps click Yes --------------- Jacob Skaria "John" wrote: 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 |
All times are GMT +1. The time now is 08:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com