| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make a cell = another cell based on a condition?
I need a number to equal the number in a cell another cell is January,
February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
Hi Don,
Thanks so much for your input. It doesn't seem to work. I don't have much experience with functions so the sumifs function may not even be what I want to use. I have a dollar amount in column C that represents a commission on a sale. Column A is months of the year. Column D is quarter 1, column E is quarter 2 and so on. I want the dollar amount in column C to transfer to column D if column A = January, February, March. "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
Don,
I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
PERFECT!!! I have been working on this for 2 days, you've made my whole
weekend!!! Thanks Sheeloo! Breezy "Sheeloo" wrote: Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
One more question. Can I add another column to the function? If Column A =
January, February, March and Column C = 2009 I want it to total in column G. If Column A = January, February, March and Column C = 2008 I want it to total in column J. Thanks in advance for any help!!! Breezy "Sheeloo" wrote: Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
=SUMPRODUCT((A4:A30={"January","February","March"} )*((C4:C30=2009)*G4:G30))
-- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... One more question. Can I add another column to the function? If Column A = January, February, March and Column C = 2009 I want it to total in column G. If Column A = January, February, March and Column C = 2008 I want it to total in column J. Thanks in advance for any help!!! Breezy "Sheeloo" wrote: Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
Try this:
=SUM(SUMIFS(G:G,A:A,{"January","February","March"} ,C:C,2009)) "breezy" wrote: One more question. Can I add another column to the function? If Column A = January, February, March and Column C = 2009 I want it to total in column G. If Column A = January, February, March and Column C = 2008 I want it to total in column J. Thanks in advance for any help!!! Breezy "Sheeloo" wrote: Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
Yes, it does.
I was in a hurry and just tested it with =SUMPRODUCT(--(A4:A30={"January","February","March"}),G4:G30) hence the long formula SUMIFS also works if you pass it to SUM as shown by Teethless Mama so you were right on ... "Don Guillett" wrote: My suggestion does work with sumproduct =SUMPRODUCT(--(A4:A30={"January","February","March"})*G4:G30) or =SUMPRODUCT((A4:A30={"January","February","March"} )*G4:G30) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
Thanks to all of you for your time this is a tremendous help!!!
Why won't my figures total 2008 when I write the function: =SUM(SUMIFS(H:H,A:A,{"January","February","March"} ,C:C,{"2009","2008"})) "Sheeloo" wrote: Yes, it does. I was in a hurry and just tested it with =SUMPRODUCT(--(A4:A30={"January","February","March"}),G4:G30) hence the long formula SUMIFS also works if you pass it to SUM as shown by Teethless Mama so you were right on ... "Don Guillett" wrote: My suggestion does work with sumproduct =SUMPRODUCT(--(A4:A30={"January","February","March"})*G4:G30) or =SUMPRODUCT((A4:A30={"January","February","March"} )*G4:G30) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
Try this:
=SUM(SUMIFS(H:H,A:A,{"January","February","March"} ,C:C,{2008;2009})) "breezy" wrote: Thanks to all of you for your time this is a tremendous help!!! Why won't my figures total 2008 when I write the function: =SUM(SUMIFS(H:H,A:A,{"January","February","March"} ,C:C,{"2009","2008"})) "Sheeloo" wrote: Yes, it does. I was in a hurry and just tested it with =SUMPRODUCT(--(A4:A30={"January","February","March"}),G4:G30) hence the long formula SUMIFS also works if you pass it to SUM as shown by Teethless Mama so you were right on ... "Don Guillett" wrote: My suggestion does work with sumproduct =SUMPRODUCT(--(A4:A30={"January","February","March"})*G4:G30) or =SUMPRODUCT((A4:A30={"January","February","March"} )*G4:G30) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
Hi,
Now you are complicating the problem =SUM(SUMIFS(H:H,A:A,{"January","February","March"} ,C:C,{"2009","2008"})) First you say you want to sum 2008 but I think you want to sum if the year is 2008 or 2009 correct? Try this =SUMPRODUCT((A:A="January")+(A:A="February")+(A:A= "March"),(C:C=2008)+(C:C=2009),H:H) -- If this helps, please click the Yes button Cheers, Shane Devenshire "breezy" wrote: Thanks to all of you for your time this is a tremendous help!!! Why won't my figures total 2008 when I write the function: =SUM(SUMIFS(H:H,A:A,{"January","February","March"} ,C:C,{"2009","2008"})) "Sheeloo" wrote: Yes, it does. I was in a hurry and just tested it with =SUMPRODUCT(--(A4:A30={"January","February","March"}),G4:G30) hence the long formula SUMIFS also works if you pass it to SUM as shown by Teethless Mama so you were right on ... "Don Guillett" wrote: My suggestion does work with sumproduct =SUMPRODUCT(--(A4:A30={"January","February","March"})*G4:G30) or =SUMPRODUCT((A4:A30={"January","February","March"} )*G4:G30) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
=SUMPRODUCT((A4:A30={"January","February","March"} )*((C4:C30=2009)+(C4:C30=2008))*G4:G30)
or =SUMPRODUCT((A4:A30={"January","February","March"} )*((C4:C30="2009")+(C4:C30="2008"))*G4:G30) depending on FORMATTING of col C -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... Thanks to all of you for your time this is a tremendous help!!! Why won't my figures total 2008 when I write the function: =SUM(SUMIFS(H:H,A:A,{"January","February","March"} ,C:C,{"2009","2008"})) "Sheeloo" wrote: Yes, it does. I was in a hurry and just tested it with =SUMPRODUCT(--(A4:A30={"January","February","March"}),G4:G30) hence the long formula SUMIFS also works if you pass it to SUM as shown by Teethless Mama so you were right on ... "Don Guillett" wrote: My suggestion does work with sumproduct =SUMPRODUCT(--(A4:A30={"January","February","March"})*G4:G30) or =SUMPRODUCT((A4:A30={"January","February","March"} )*G4:G30) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I make a cell = another cell based on a condition?
You all have helped me build my template thanks so much!!! I have another
question. The template has information on it that needs to automatically transfer to different workbooks. Such as: If column G = a specific name then I need certain items in that row to automatically transfer to the workbook that belongs to the specific name. Can this be done? I don't know if I'm making sense.... "Teethless mama" wrote: Try this: =SUM(SUMIFS(H:H,A:A,{"January","February","March"} ,C:C,{2008;2009})) "breezy" wrote: Thanks to all of you for your time this is a tremendous help!!! Why won't my figures total 2008 when I write the function: =SUM(SUMIFS(H:H,A:A,{"January","February","March"} ,C:C,{"2009","2008"})) "Sheeloo" wrote: Yes, it does. I was in a hurry and just tested it with =SUMPRODUCT(--(A4:A30={"January","February","March"}),G4:G30) hence the long formula SUMIFS also works if you pass it to SUM as shown by Teethless Mama so you were right on ... "Don Guillett" wrote: My suggestion does work with sumproduct =SUMPRODUCT(--(A4:A30={"January","February","March"})*G4:G30) or =SUMPRODUCT((A4:A30={"January","February","March"} )*G4:G30) -- Don Guillett Microsoft MVP Excel SalesAid Software "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Don, I tested (with Excel 2007) the way you suggested but it did not work. I believe that SUMIFS evaluates conditions with AND (all conditions should be true) One solution, in this limited case, would be =SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30)) "Don Guillett" wrote: I haven't worked with sumIFS but try ={"January","February","March"} -- Don Guillett Microsoft MVP Excel SalesAid Software "breezy" wrote in message ... I need a number to equal the number in a cell another cell is January, February, or March. I can make it work using only one month... =SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving to cell based on condition of another cell | Excel Discussion (Misc queries) | |||
| Hide cell values based on a condition in another cell | Excel Worksheet Functions | |||
| Fill a cell based on a condition being met | Excel Worksheet Functions | |||
| I want to fill the cell color based upon the other cell condition | Excel Discussion (Misc queries) | |||
| make a cell empty based on condition | Charts and Charting in Excel | |||
Linear Mode
