Sort with IF functions?

[David P.]

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

 

[Brad]

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....

 

[David P.]

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

 

[David P.]

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.

 

[Brad]

Glad you got it to work - I was using the followin

 

[Tylerpestell]

Sorry for hijacking your post but I am looking for a David P that worked on
the program MSAT