Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex SUMIF
First...this site is great. Such positive feedback responses and quick too.
I am looking for formula help. I have a list of "Names" in Cells A1:A15 (e.g. A1=John, A2=Jane, A3=John, A4=Fred, A5=John€¦). In Column B, I have a list of "Last Names" in Cells B1:B15 (e.g. B1=Smith, B2=Jones, B3=Edwards, B4=Smith, B5=Smith...). In Column C, I have a list of numbers in Cells C1:C15 (e.g. C1=1,C2=2,C3=3,C4=4,C5=5,...). I would like to set up a formula in Column D where I indicate that if the criteria in Column A is "John" and Column B criteria is "Smith" then sum the numbers in Column C. So in my example, there are two instances of "John Smith." The numbers should total 6 (1+5). Is there a formula for this? For some reason I cannot get a SUMIF formula to work. To further complicate matters, I need the formula to ignore blanks. Help...please! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex SUMIF
Try this:
=SUMPRODUCT(--(A1:A15="John"),--(B1:B15="Smith"),C1:C15) HTH, Paul -- "Evan" wrote in message ... First...this site is great. Such positive feedback responses and quick too. I am looking for formula help. I have a list of "Names" in Cells A1:A15 (e.g. A1=John, A2=Jane, A3=John, A4=Fred, A5=John.). In Column B, I have a list of "Last Names" in Cells B1:B15 (e.g. B1=Smith, B2=Jones, B3=Edwards, B4=Smith, B5=Smith...). In Column C, I have a list of numbers in Cells C1:C15 (e.g. C1=1,C2=2,C3=3,C4=4,C5=5,...). I would like to set up a formula in Column D where I indicate that if the criteria in Column A is "John" and Column B criteria is "Smith" then sum the numbers in Column C. So in my example, there are two instances of "John Smith." The numbers should total 6 (1+5). Is there a formula for this? For some reason I cannot get a SUMIF formula to work. To further complicate matters, I need the formula to ignore blanks. Help...please! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex SUMIF
Hi
Try =SUMPRODUCT(--($A1:$A$100="John"),--($B$1:$B$100="Smith"),$C$1:$C$100) You could put the John and Smith in separate cells e.g. D1 and EI and use =SUMPRODUCT(--($A1:$A$100=$D$1),--($B$1:$B$100=$E$1),$C$1:$C$100) then just changing the values in D1 and E1 will give you results for other people. -- Regards Roger Govier "Evan" wrote in message ... First...this site is great. Such positive feedback responses and quick too. I am looking for formula help. I have a list of "Names" in Cells A1:A15 (e.g. A1=John, A2=Jane, A3=John, A4=Fred, A5=John.). In Column B, I have a list of "Last Names" in Cells B1:B15 (e.g. B1=Smith, B2=Jones, B3=Edwards, B4=Smith, B5=Smith...). In Column C, I have a list of numbers in Cells C1:C15 (e.g. C1=1,C2=2,C3=3,C4=4,C5=5,...). I would like to set up a formula in Column D where I indicate that if the criteria in Column A is "John" and Column B criteria is "Smith" then sum the numbers in Column C. So in my example, there are two instances of "John Smith." The numbers should total 6 (1+5). Is there a formula for this? For some reason I cannot get a SUMIF formula to work. To further complicate matters, I need the formula to ignore blanks. Help...please! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex SUMIF
Thanks everyone!!! These all worked. I appreciate the quick responses
"Roger Govier" wrote: Hi Try =SUMPRODUCT(--($A1:$A$100="John"),--($B$1:$B$100="Smith"),$C$1:$C$100) You could put the John and Smith in separate cells e.g. D1 and EI and use =SUMPRODUCT(--($A1:$A$100=$D$1),--($B$1:$B$100=$E$1),$C$1:$C$100) then just changing the values in D1 and E1 will give you results for other people. -- Regards Roger Govier "Evan" wrote in message ... First...this site is great. Such positive feedback responses and quick too. I am looking for formula help. I have a list of "Names" in Cells A1:A15 (e.g. A1=John, A2=Jane, A3=John, A4=Fred, A5=John.). In Column B, I have a list of "Last Names" in Cells B1:B15 (e.g. B1=Smith, B2=Jones, B3=Edwards, B4=Smith, B5=Smith...). In Column C, I have a list of numbers in Cells C1:C15 (e.g. C1=1,C2=2,C3=3,C4=4,C5=5,...). I would like to set up a formula in Column D where I indicate that if the criteria in Column A is "John" and Column B criteria is "Smith" then sum the numbers in Column C. So in my example, there are two instances of "John Smith." The numbers should total 6 (1+5). Is there a formula for this? For some reason I cannot get a SUMIF formula to work. To further complicate matters, I need the formula to ignore blanks. Help...please! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex SUMIF question | Excel Worksheet Functions | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
How to use complex criteria in SUMIF() | Excel Worksheet Functions | |||
Can I use more complex logical expression for sumif as creteria? | Excel Worksheet Functions | |||
SumIf formula with complex criteria | New Users to Excel |