Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - If and Subtotal(???)
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 numbers in Cells B1:B15 (e.g. B1=1,B2=2,B3=3,B4=5,B5=5,...). I would like to set up a formula in Column C where I indicate that IF Column A contains John, then sum the numbers in Column B that are adjacent to John's name. So in my example, there are three instances of "John." The numbers adjacent to John's name total 9 (1+3+5). Is there a formula for this. My guess is a subtotal or sumif formula nested inside an IF formula but I could be mistaken. Everything I tried so far does not work. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - If and Subtotal(???)
Look into the SumIf function, will let you specify thename to look for and
add what is in the column by them. -- -John Please rate when your question is answered to help us and others know what is helpful. "Evan" wrote: 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 numbers in Cells B1:B15 (e.g. B1=1,B2=2,B3=3,B4=5,B5=5,...). I would like to set up a formula in Column C where I indicate that IF Column A contains John, then sum the numbers in Column B that are adjacent to John's name. So in my example, there are three instances of "John." The numbers adjacent to John's name total 9 (1+3+5). Is there a formula for this. My guess is a subtotal or sumif formula nested inside an IF formula but I could be mistaken. Everything I tried so far does not work. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - If and Subtotal(???)
=IF(COUNTIF(A1:A15,"John")0,SUMIF(A1:A15,"John",B 1:B15),"no John")
I would personally use =SUMIF(A1:A15,D1,B1:B15) and put the names in D1 -- Regards, Peo Sjoblom "Evan" wrote in message ... 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 numbers in Cells B1:B15 (e.g. B1=1,B2=2,B3=3,B4=5,B5=5,...). I would like to set up a formula in Column C where I indicate that IF Column A contains John, then sum the numbers in Column B that are adjacent to John's name. So in my example, there are three instances of "John." The numbers adjacent to John's name total 9 (1+3+5). Is there a formula for this. My guess is a subtotal or sumif formula nested inside an IF formula but I could be mistaken. Everything I tried so far does not work. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - If and Subtotal(???)
=SUMIF(A:A,"John",B:B)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Evan" wrote in message ... 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 numbers in Cells B1:B15 (e.g. B1=1,B2=2,B3=3,B4=5,B5=5,...). I would like to set up a formula in Column C where I indicate that IF Column A contains John, then sum the numbers in Column B that are adjacent to John's name. So in my example, there are three instances of "John." The numbers adjacent to John's name total 9 (1+3+5). Is there a formula for this. My guess is a subtotal or sumif formula nested inside an IF formula but I could be mistaken. Everything I tried so far does not work. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help - If and Subtotal(???)
Thanks so much Bob and Peo!! Both worked. I really appreciate it
"Bob Phillips" wrote: =SUMIF(A:A,"John",B:B) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Evan" wrote in message ... 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 numbers in Cells B1:B15 (e.g. B1=1,B2=2,B3=3,B4=5,B5=5,...). I would like to set up a formula in Column C where I indicate that IF Column A contains John, then sum the numbers in Column B that are adjacent to John's name. So in my example, there are three instances of "John." The numbers adjacent to John's name total 9 (1+3+5). Is there a formula for this. My guess is a subtotal or sumif formula nested inside an IF formula but I could be mistaken. Everything I tried so far does not work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal Formula | Excel Worksheet Functions | |||
Subtotal formula | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Help with SUBTOTAL formula | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |