| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM IF with Array
Hi
I want to ADD numbers using multiple conditions. Andy Mark MTD 10 YTD 20 CTD 30 Andy Mark MTD 40 YTD 50 CTD 60 Jhon Mark MTD 70 YTD 80 CTD 90 Now here i want to add numbers of D coloumn If Coloumn 'A' has 'Andy' , Coloumn 'B' has 'Mark' and Coloumn 'C' has YTD Rember A2, A3, A5, A6, B2, B3, B5 and B6 cells are blank. If I repeat Andy and Mark in each row then its very easy to sum but i dont want to do that. The formula should return the result (20 + 50) = 70 I want to add value for 'Andy' 'Mark' and 'YTD' and i dont want to repeat andy and mark's name Could you please let me know the solution. Thanks Milind |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM IF with Array
oooppsss formatting issue
Andy---------Mark-------MTD-----10 <Blank------<Blank----YTD-----20 <Blank-------<Blank---CTD.........30 Andy--------Mark------- MTD-----40 <Blank-----<Blank----YTD------50 <Blank-----<Blank----CTD------60 Jhon---------Mark------- MTD------ 70 <Blank-----<Blank----YTD------- 80 <Blank-----<Blank----CTD------ 90 Now here i want to add numbers of D coloumn If Coloumn 'A' has 'Andy' , Coloumn 'B' has 'Mark' and Coloumn 'C' has YTD Rember A2, A3, A5, A6, B2, B3, B5 and B6 cells are blank. If I repeat Andy and Mark in each row then its very easy to sum but i dont want to do that. The formula should return the result (20 + 50) = 70 I want to add value for 'Andy' 'Mark' and 'YTD' and i dont want to repeat andy and mark's name Could you please let me know the solution. Thanks Milind |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM IF with Array
I would add those names to each row using the techniques at Debra Dalgleish's
site: http://contextures.com/xlDataEntry02.html or http://www.contextures.com/xlVideos01.html#FillBlanks Then hide the duplicates by using this: http://contextures.com/xlCondFormat03.html#Duplicate I think it makes things lots easier. Milind Keer wrote: oooppsss formatting issue Andy---------Mark-------MTD-----10 <Blank------<Blank----YTD-----20 <Blank-------<Blank---CTD.........30 Andy--------Mark------- MTD-----40 <Blank-----<Blank----YTD------50 <Blank-----<Blank----CTD------60 Jhon---------Mark------- MTD------ 70 <Blank-----<Blank----YTD------- 80 <Blank-----<Blank----CTD------ 90 Now here i want to add numbers of D coloumn If Coloumn 'A' has 'Andy' , Coloumn 'B' has 'Mark' and Coloumn 'C' has YTD Rember A2, A3, A5, A6, B2, B3, B5 and B6 cells are blank. If I repeat Andy and Mark in each row then its very easy to sum but i dont want to do that. The formula should return the result (20 + 50) = 70 I want to add value for 'Andy' 'Mark' and 'YTD' and i dont want to repeat andy and mark's name Could you please let me know the solution. Thanks Milind -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM IF with Array
Hi Dave
Thanks for the prompt reply. I did think of similar solution but the problem is my data contains 1000s of rows and each time i get different sets of data and this solution wont work for dat... I can write a macro to dis... i mean i guess simple FOR LOOP would easily take of dis.... but i just wondering can we do it using formulas... thanks again!! Milind "Dave Peterson" wrote: I would add those names to each row using the techniques at Debra Dalgleish's site: http://contextures.com/xlDataEntry02.html or http://www.contextures.com/xlVideos01.html#FillBlanks Then hide the duplicates by using this: http://contextures.com/xlCondFormat03.html#Duplicate I think it makes things lots easier. Milind Keer wrote: oooppsss formatting issue Andy---------Mark-------MTD-----10 <Blank------<Blank----YTD-----20 <Blank-------<Blank---CTD.........30 Andy--------Mark------- MTD-----40 <Blank-----<Blank----YTD------50 <Blank-----<Blank----CTD------60 Jhon---------Mark------- MTD------ 70 <Blank-----<Blank----YTD------- 80 <Blank-----<Blank----CTD------ 90 Now here i want to add numbers of D coloumn If Coloumn 'A' has 'Andy' , Coloumn 'B' has 'Mark' and Coloumn 'C' has YTD Rember A2, A3, A5, A6, B2, B3, B5 and B6 cells are blank. If I repeat Andy and Mark in each row then its very easy to sum but i dont want to do that. The formula should return the result (20 + 50) = 70 I want to add value for 'Andy' 'Mark' and 'YTD' and i dont want to repeat andy and mark's name Could you please let me know the solution. Thanks Milind -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM IF with Array
The code at Debra's site (using specialcells) would probably be faster than
looping through lots and lots of cells. I don't know of any formula that would do what you want. I'd bet it would be pretty complex if, er, when someone suggests one. Milind Keer wrote: Hi Dave Thanks for the prompt reply. I did think of similar solution but the problem is my data contains 1000s of rows and each time i get different sets of data and this solution wont work for dat... I can write a macro to dis... i mean i guess simple FOR LOOP would easily take of dis.... but i just wondering can we do it using formulas... thanks again!! Milind "Dave Peterson" wrote: I would add those names to each row using the techniques at Debra Dalgleish's site: http://contextures.com/xlDataEntry02.html or http://www.contextures.com/xlVideos01.html#FillBlanks Then hide the duplicates by using this: http://contextures.com/xlCondFormat03.html#Duplicate I think it makes things lots easier. Milind Keer wrote: oooppsss formatting issue Andy---------Mark-------MTD-----10 <Blank------<Blank----YTD-----20 <Blank-------<Blank---CTD.........30 Andy--------Mark------- MTD-----40 <Blank-----<Blank----YTD------50 <Blank-----<Blank----CTD------60 Jhon---------Mark------- MTD------ 70 <Blank-----<Blank----YTD------- 80 <Blank-----<Blank----CTD------ 90 Now here i want to add numbers of D coloumn If Coloumn 'A' has 'Andy' , Coloumn 'B' has 'Mark' and Coloumn 'C' has YTD Rember A2, A3, A5, A6, B2, B3, B5 and B6 cells are blank. If I repeat Andy and Mark in each row then its very easy to sum but i dont want to do that. The formula should return the result (20 + 50) = 70 I want to add value for 'Andy' 'Mark' and 'YTD' and i dont want to repeat andy and mark's name Could you please let me know the solution. Thanks Milind -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM IF with Array
Hi Dave
ya!! even i think its not that easy to write any fornula for below condition... Evry month i m getting different sets of data the overall template is same but data and no of rows are different.... anywayz thanks a lot for your advise and lets wait for reply from some XL GURU thanks again!! Milind "Dave Peterson" wrote: The code at Debra's site (using specialcells) would probably be faster than looping through lots and lots of cells. I don't know of any formula that would do what you want. I'd bet it would be pretty complex if, er, when someone suggests one. Milind Keer wrote: Hi Dave Thanks for the prompt reply. I did think of similar solution but the problem is my data contains 1000s of rows and each time i get different sets of data and this solution wont work for dat... I can write a macro to dis... i mean i guess simple FOR LOOP would easily take of dis.... but i just wondering can we do it using formulas... thanks again!! Milind "Dave Peterson" wrote: I would add those names to each row using the techniques at Debra Dalgleish's site: http://contextures.com/xlDataEntry02.html or http://www.contextures.com/xlVideos01.html#FillBlanks Then hide the duplicates by using this: http://contextures.com/xlCondFormat03.html#Duplicate I think it makes things lots easier. Milind Keer wrote: oooppsss formatting issue Andy---------Mark-------MTD-----10 <Blank------<Blank----YTD-----20 <Blank-------<Blank---CTD.........30 Andy--------Mark------- MTD-----40 <Blank-----<Blank----YTD------50 <Blank-----<Blank----CTD------60 Jhon---------Mark------- MTD------ 70 <Blank-----<Blank----YTD------- 80 <Blank-----<Blank----CTD------ 90 Now here i want to add numbers of D coloumn If Coloumn 'A' has 'Andy' , Coloumn 'B' has 'Mark' and Coloumn 'C' has YTD Rember A2, A3, A5, A6, B2, B3, B5 and B6 cells are blank. If I repeat Andy and Mark in each row then its very easy to sum but i dont want to do that. The formula should return the result (20 + 50) = 70 I want to add value for 'Andy' 'Mark' and 'YTD' and i dont want to repeat andy and mark's name Could you please let me know the solution. Thanks Milind -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Populate an array that is a subset of a larger array? | Excel Worksheet Functions | |||
| Array: Counting multiple values within array | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
| Goal Seek On Members of an Array within Array | Excel Worksheet Functions | |||
Linear Mode
