| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function for an array | Excel Worksheet Functions | |||
| Array / Function HELP!!! | Excel Worksheet Functions | |||
| OR function in array-entered IF function | Excel Worksheet Functions | |||
| need some help with an array function | Excel Discussion (Misc queries) | |||
| array function | Excel Discussion (Misc queries) | |||
Linear Mode
