| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with IF functions?
I didn't know what to put in the subject. Here is what I hope to do. As you
go down column B each time the cell equals false (based on an if function) it will place the value in Column A (in the same row) one column over to the right. This will allow me to add up each column seperately when it is sorted this way. How in the world can this be done without some crazy macro? See an explantion below. I would arrive at this type of scenario after an alphabetical sort: Column A B C D E F A1 100 TRUE 100 A2 200 TRUE 200 A3 300 FALSE 300 A4 400 TRUE 400 A5 500 TRUE 500 A6 600 FALSE 600 A7 700 TRUE 700 A8 800 TRUE 800 A9 900 FALSE 900 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort with IF functions?
Without using macros - an alternate solution would be to have a "helper" Column
with the formula in C1 the value 1 In C2 =C1+(--AND(B1="TRUE",B2="FALSE")) copy down And then in column D =SUMIF(C1:C9,1,A1:A9) - will give you the value 300 Changing the ,1, to ,2, - will give you the value 1200 Not exactly in the format you asked for but will give you the desired results. If you want all the sums at once - copy the equation down several time - make sure you lock down the ranges (ie $c$1:$c$9 and $a$1:$a$9) and let the middle number change.... -- Wag more, bark less "David P." wrote: I didn't know what to put in the subject. Here is what I hope to do. As you go down column B each time the cell equals false (based on an if function) it will place the value in Column A (in the same row) one column over to the right. This will allow me to add up each column seperately when it is sorted this way. How in the world can this be done without some crazy macro? See an explantion below. I would arrive at this type of scenario after an alphabetical sort: Column A B C D E F A1 100 TRUE 100 A2 200 TRUE 200 A3 300 FALSE 300 A4 400 TRUE 400 A5 500 TRUE 500 A6 600 FALSE 600 A7 700 TRUE 700 A8 800 TRUE 800 A9 900 FALSE 900 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort with IF functions?
Hi Brad,
Thank you for the suggestion. I tried to understand it but I wasn't able to get the same result. Just so you know I have not told you what the TRUE & FALSE is determined by but I left it out so as to not confuse it even more. Again I need to be able to add up the values in column "A" up until it's neighboring value in column "B" is FALSE. In this case below I need to be able to automatically add up A1 & A2 separate from A3, A4, & A5 separate from A5, A6, & A7 separate from A8 (by itself). My spreadsheet will not always have the TRUE, TRUE, FALSE as you go down the "B" column but to make the example easier I just put it that way. Essentially I need it to add up the "A" column stopping one cell before the B column equals FALSE. If your suggestion does this do you mean: A B C D 100 TRUE 1 =SUMIF($C$1:$C$9,1,$A$1:$A$9) 200 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 300 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 400 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 500 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 600 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 700 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 800 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 900 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF "Brad" wrote: Without using macros - an alternate solution would be to have a "helper" Column with the formula in C1 the value 1 In C2 =C1+(--AND(B1="TRUE",B2="FALSE")) copy down And then in column D =SUMIF(C1:C9,1,A1:A9) - will give you the value 300 Changing the ,1, to ,2, - will give you the value 1200 Not exactly in the format you asked for but will give you the desired results. If you want all the sums at once - copy the equation down several time - make sure you lock down the ranges (ie $c$1:$c$9 and $a$1:$a$9) and let the middle number change.... -- Wag more, bark less "David P." wrote: I didn't know what to put in the subject. Here is what I hope to do. As you go down column B each time the cell equals false (based on an if function) it will place the value in Column A (in the same row) one column over to the right. This will allow me to add up each column seperately when it is sorted this way. How in the world can this be done without some crazy macro? See an explantion below. I would arrive at this type of scenario after an alphabetical sort: Column A B C D E F A1 100 TRUE 100 A2 200 TRUE 200 A3 300 FALSE 300 A4 400 TRUE 400 A5 500 TRUE 500 A6 600 FALSE 600 A7 700 TRUE 700 A8 800 TRUE 800 A9 900 FALSE 900 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort with IF functions?
Brad I think I figured out another way to do it with your SUMIF function you
used. Don't spend anymore time on this because I think I've got it now. Many thanks you triggered me onto the right solution. "David P." wrote: Hi Brad, Thank you for the suggestion. I tried to understand it but I wasn't able to get the same result. Just so you know I have not told you what the TRUE & FALSE is determined by but I left it out so as to not confuse it even more. Again I need to be able to add up the values in column "A" up until it's neighboring value in column "B" is FALSE. In this case below I need to be able to automatically add up A1 & A2 separate from A3, A4, & A5 separate from A5, A6, & A7 separate from A8 (by itself). My spreadsheet will not always have the TRUE, TRUE, FALSE as you go down the "B" column but to make the example easier I just put it that way. Essentially I need it to add up the "A" column stopping one cell before the B column equals FALSE. If your suggestion does this do you mean: A B C D 100 TRUE 1 =SUMIF($C$1:$C$9,1,$A$1:$A$9) 200 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 300 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 400 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 500 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 600 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 700 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 800 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 900 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF "Brad" wrote: Without using macros - an alternate solution would be to have a "helper" Column with the formula in C1 the value 1 In C2 =C1+(--AND(B1="TRUE",B2="FALSE")) copy down And then in column D =SUMIF(C1:C9,1,A1:A9) - will give you the value 300 Changing the ,1, to ,2, - will give you the value 1200 Not exactly in the format you asked for but will give you the desired results. If you want all the sums at once - copy the equation down several time - make sure you lock down the ranges (ie $c$1:$c$9 and $a$1:$a$9) and let the middle number change.... -- Wag more, bark less "David P." wrote: I didn't know what to put in the subject. Here is what I hope to do. As you go down column B each time the cell equals false (based on an if function) it will place the value in Column A (in the same row) one column over to the right. This will allow me to add up each column seperately when it is sorted this way. How in the world can this be done without some crazy macro? See an explantion below. I would arrive at this type of scenario after an alphabetical sort: Column A B C D E F A1 100 TRUE 100 A2 200 TRUE 200 A3 300 FALSE 300 A4 400 TRUE 400 A5 500 TRUE 500 A6 600 FALSE 600 A7 700 TRUE 700 A8 800 TRUE 800 A9 900 FALSE 900 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort with IF functions?
Glad you got it to work - I was using the followin
200 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) 300 FALSE =$C$1+(--AND(B2="TRUE",B3="FALSE")) 400 TRUE =$C$1+(--AND(B3="TRUE",B4="FALSE")) 500 TRUE =$C$1+(--AND(B4="TRUE",B5="FALSE")) 600 FALSE =$C$1+(--AND(B5="TRUE",B6="FALSE")) 700 TRUE =$C$1+(--AND(B6="TRUE",B7="FALSE")) 800 TRUE =$C$1+(--AND(B7="TRUE",B8="FALSE")) 900 FALSE =$C$1+(--AND(B8="TRUE",B9="FALSE")) -- Wag more, bark less "David P." wrote: Hi Brad, Thank you for the suggestion. I tried to understand it but I wasn't able to get the same result. Just so you know I have not told you what the TRUE & FALSE is determined by but I left it out so as to not confuse it even more. Again I need to be able to add up the values in column "A" up until it's neighboring value in column "B" is FALSE. In this case below I need to be able to automatically add up A1 & A2 separate from A3, A4, & A5 separate from A5, A6, & A7 separate from A8 (by itself). My spreadsheet will not always have the TRUE, TRUE, FALSE as you go down the "B" column but to make the example easier I just put it that way. Essentially I need it to add up the "A" column stopping one cell before the B column equals FALSE. If your suggestion does this do you mean: A B C D 100 TRUE 1 =SUMIF($C$1:$C$9,1,$A$1:$A$9) 200 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 300 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 400 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 500 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 600 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 700 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 800 TRUE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF 900 FALSE =$C$1+(--AND(B1="TRUE",B2="FALSE")) Copy the above =SUMIF "Brad" wrote: Without using macros - an alternate solution would be to have a "helper" Column with the formula in C1 the value 1 In C2 =C1+(--AND(B1="TRUE",B2="FALSE")) copy down And then in column D =SUMIF(C1:C9,1,A1:A9) - will give you the value 300 Changing the ,1, to ,2, - will give you the value 1200 Not exactly in the format you asked for but will give you the desired results. If you want all the sums at once - copy the equation down several time - make sure you lock down the ranges (ie $c$1:$c$9 and $a$1:$a$9) and let the middle number change.... -- Wag more, bark less "David P." wrote: I didn't know what to put in the subject. Here is what I hope to do. As you go down column B each time the cell equals false (based on an if function) it will place the value in Column A (in the same row) one column over to the right. This will allow me to add up each column seperately when it is sorted this way. How in the world can this be done without some crazy macro? See an explantion below. I would arrive at this type of scenario after an alphabetical sort: Column A B C D E F A1 100 TRUE 100 A2 200 TRUE 200 A3 300 FALSE 300 A4 400 TRUE 400 A5 500 TRUE 500 A6 600 FALSE 600 A7 700 TRUE 700 A8 800 TRUE 800 A9 900 FALSE 900 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort with IF functions?
Sorry for hijacking your post but I am looking for a David P that worked on
the program MSAT |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort sheet based on particuilar sort order | Excel Worksheet Functions | |||
| sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
| efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
| Automatic Sort with VLOOKUP/LARGE Functions | Excel Worksheet Functions | |||
| Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Linear Mode
