|
need help with a formula
I need help with a formula. If cells C12 through AG 12 have an "A" or an "F"
listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
=SUMIF(C12:AG12,"A",C13:AG13)+SUMIF(C12:AG12,"F",C 13:AG13)
=SUMIF(C12:AG12,"T",C13:AG13) -- David Biddulph "Rhonda Edwards" wrote in message ... I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
One way
For your first question use =SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13)) Happy new year From New Zealand "Rhonda Edwards" wrote in message ... I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
Or even something in this form
=SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13)) Happy New Year from Nova Scotia -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bill Kuunders" wrote in message ... One way For your first question use =SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13)) Happy new year From New Zealand "Rhonda Edwards" wrote in message ... I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
Hi Rhonda,
You have a simple formula for one condition already. For the situation of A or F you can use: =SUMPRODUCT(((C12:AG12="A")+(C12:AG12="F"))*C13:AG 13) Or the more sophisticated: =SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) -- Cheers, Shane Devenshire "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
Thanks Bernard
I tried to shorten it, but was using the old fashioned * format. Still learning over here......... Bill "Bernard Liengme" wrote in message ... Or even something in this form =SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13)) Happy New Year from Nova Scotia -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bill Kuunders" wrote in message ... One way For your first question use =SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13)) Happy new year From New Zealand "Rhonda Edwards" wrote in message ... I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
=SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13))
Another one: =SUMPRODUCT(--(ISNUMBER(MATCH(C12:AG12,{"A","F"},0))),C13:AG13) Biff "ShaneDevenshire" wrote in message ... Hi Rhonda, You have a simple formula for one condition already. For the situation of A or F you can use: =SUMPRODUCT(((C12:AG12="A")+(C12:AG12="F"))*C13:AG 13) Or the more sophisticated: =SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) -- Cheers, Shane Devenshire "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
Hello Bill
In the expression (range1)*(range2) the * operator can be thought of as AND while (range1)+(range2) the + operator is OR Think about {1, 0, 1}*{1, 1, 0}; we get {1, 0, 0} when each element in the first array is multiplied by the correspond element in the second array. We get 1 in the position were BOTH arrays have a 1. While {1, 0, 1}+{1, 1, 0} yields {1, 1, 1} when each element in the first array is added (binary adding giving either 0 or 1) to the correspond element in the second array. We get a 1 in any position were EITHER array has a 1. My last 'lecture' for 2006!!! hope this helps all the best -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bill Kuunders" wrote in message ... Thanks Bernard I tried to shorten it, but was using the old fashioned * format. Still learning over here......... Bill "Bernard Liengme" wrote in message ... Or even something in this form =SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13)) Happy New Year from Nova Scotia -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bill Kuunders" wrote in message ... One way For your first question use =SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13)) Happy new year From New Zealand "Rhonda Edwards" wrote in message ... I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
Try this:
SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
Well I see I started something with the {"F","A"}.
Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and =SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical. However, I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) to work and I'm not sure I follow the logic? -- Thanks, Shane Devenshire "Teethless mama" wrote: Try this: SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)
Don't worry about it. It won't work. It's not syntatically correct for one thing and even if you correct that, it still won't work. Biff "ShaneDevenshire" wrote in message ... Well I see I started something with the {"F","A"}. Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and =SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical. However, I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) to work and I'm not sure I follow the logic? -- Thanks, Shane Devenshire "Teethless mama" wrote: Try this: SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
Fellas ... You're all forgetting that you're going *across*, not down!
This works fine for me: =SUMPRODUCT((C12:AG12={"A";"F"})*C13:AG13) Hit <F9 after selecting "C12:AG12={"A","F"}", And you see only 2 returns, with the rest being #N/A, meaning that the formula is polling down. The semi-colon just tells it to poll across (I THINK)!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) Don't worry about it. It won't work. It's not syntatically correct for one thing and even if you correct that, it still won't work. Biff "ShaneDevenshire" wrote in message ... Well I see I started something with the {"F","A"}. Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and =SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical. However, I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) to work and I'm not sure I follow the logic? -- Thanks, Shane Devenshire "Teethless mama" wrote: Try this: SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
I didn't even think of that!
Biff "Ragdyer" wrote in message ... Fellas ... You're all forgetting that you're going *across*, not down! This works fine for me: =SUMPRODUCT((C12:AG12={"A";"F"})*C13:AG13) Hit <F9 after selecting "C12:AG12={"A","F"}", And you see only 2 returns, with the rest being #N/A, meaning that the formula is polling down. The semi-colon just tells it to poll across (I THINK)!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) Don't worry about it. It won't work. It's not syntatically correct for one thing and even if you correct that, it still won't work. Biff "ShaneDevenshire" wrote in message ... Well I see I started something with the {"F","A"}. Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and =SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical. However, I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) to work and I'm not sure I follow the logic? -- Thanks, Shane Devenshire "Teethless mama" wrote: Try this: SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
That's rare for you!<bg
HAPPY NEW YEAR Biff -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... I didn't even think of that! Biff "Ragdyer" wrote in message ... Fellas ... You're all forgetting that you're going *across*, not down! This works fine for me: =SUMPRODUCT((C12:AG12={"A";"F"})*C13:AG13) Hit <F9 after selecting "C12:AG12={"A","F"}", And you see only 2 returns, with the rest being #N/A, meaning that the formula is polling down. The semi-colon just tells it to poll across (I THINK)!<g -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "T. Valko" wrote in message ... I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) Don't worry about it. It won't work. It's not syntatically correct for one thing and even if you correct that, it still won't work. Biff "ShaneDevenshire" wrote in message ... Well I see I started something with the {"F","A"}. Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and =SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical. However, I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) to work and I'm not sure I follow the logic? -- Thanks, Shane Devenshire "Teethless mama" wrote: Try this: SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
So that's what I was doing wrong!! I was thinking that I've seen a way to do
this w/sumproduct and an array constant, but couldn't get it. Thanks for posting, Ragdyer, that was bugging me. "Ragdyer" wrote: Fellas ... You're all forgetting that you're going *across*, not down! This works fine for me: =SUMPRODUCT((C12:AG12={"A";"F"})*C13:AG13) Hit <F9 after selecting "C12:AG12={"A","F"}", And you see only 2 returns, with the rest being #N/A, meaning that the formula is polling down. The semi-colon just tells it to poll across (I THINK)!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) Don't worry about it. It won't work. It's not syntatically correct for one thing and even if you correct that, it still won't work. Biff "ShaneDevenshire" wrote in message ... Well I see I started something with the {"F","A"}. Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and =SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical. However, I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) to work and I'm not sure I follow the logic? -- Thanks, Shane Devenshire "Teethless mama" wrote: Try this: SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13) "Rhonda Edwards" wrote: I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
Thanks Bernard
I feel honoured ........ This is why I follow these groups, You take the extra effort to explain different issues. Bill "Bernard Liengme" wrote in message ... Hello Bill In the expression (range1)*(range2) the * operator can be thought of as AND while (range1)+(range2) the + operator is OR Think about {1, 0, 1}*{1, 1, 0}; we get {1, 0, 0} when each element in the first array is multiplied by the correspond element in the second array. We get 1 in the position were BOTH arrays have a 1. While {1, 0, 1}+{1, 1, 0} yields {1, 1, 1} when each element in the first array is added (binary adding giving either 0 or 1) to the correspond element in the second array. We get a 1 in any position were EITHER array has a 1. My last 'lecture' for 2006!!! hope this helps all the best -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bill Kuunders" wrote in message ... Thanks Bernard I tried to shorten it, but was using the old fashioned * format. Still learning over here......... Bill "Bernard Liengme" wrote in message ... Or even something in this form =SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13)) Happy New Year from Nova Scotia -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bill Kuunders" wrote in message ... One way For your first question use =SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13)) Happy new year From New Zealand "Rhonda Edwards" wrote in message ... I need help with a formula. If cells C12 through AG 12 have an "A" or an "F" listed in any cell, I want to add and total the actual whole numbers listed in cells C13 through AG13 (that have A or F above them in the column)totaling them in cell in AH (where I merged the cells 11-13). Then in AI, totaling all the numbers in C13 through AG13 that have "T" listed in any cell from C12 through AG 12. And so on. I have tried to figure this out from reading the help menu in Excel but I haven't been able to; can someone help. Thanks, -- Rhonda -- Rhonda |
need help with a formula
sorry i am using this way to cammunicate my question bcz i dont know how i
can post my new question in this group. if any body can help kindly mail me at . my question is as follows Sheet A has Col A: Inventory code Col B: Description Col C: Quantity Sheet B has Col A: Inventory code Col B: Description Col C: Quantity ( both sheets have same data but inventory code may be different) Sheet C required Required: total quantity of sheet A and sheet B in Col. C of sheet C Kindly help me to create a logical formula / vlook formula Example Sheet A Col A Col B Col C Inventory code Description Quantity 10001 pencil 25 10002 pen 50 10003 rubber 75 Sheet B Col A Col B Col C Inventory code Description Quantity 10001 pencil 25 10002 pen 50 10004 Duster 15 Sheet C Col A Col B Col C Inventory code Description Total Qty of sheet A & Sheet B 10001 pencil ? ( total required with the help of vlook formula) 10002 pen ? 10003 rubber ? 10004 Duster ? |
need help with a formula
I've given you an answer to your duplicate posting in another group.
Pete vlook fomula wrote: sorry i am using this way to cammunicate my question bcz i dont know how i can post my new question in this group. if any body can help kindly mail me at . my question is as follows Sheet A has Col A: Inventory code Col B: Description Col C: Quantity Sheet B has Col A: Inventory code Col B: Description Col C: Quantity ( both sheets have same data but inventory code may be different) Sheet C required Required: total quantity of sheet A and sheet B in Col. C of sheet C Kindly help me to create a logical formula / vlook formula Example Sheet A Col A Col B Col C Inventory code Description Quantity 10001 pencil 25 10002 pen 50 10003 rubber 75 Sheet B Col A Col B Col C Inventory code Description Quantity 10001 pencil 25 10002 pen 50 10004 Duster 15 Sheet C Col A Col B Col C Inventory code Description Total Qty of sheet A & Sheet B 10001 pencil ? ( total required with the help of vlook formula) 10002 pen ? 10003 rubber ? 10004 Duster ? |
| All times are GMT +1. The time now is 07:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com