Count TRUE only

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

The following formula will return a value(s) based on BK106 being TRUE
If BK106 is FALSE it will return nothing.

=IF(BK106,(SUBSTITUTE(TRIM(BR106&" "&BS106),"",",")),IF(NOT(BK106),"",""))

I want to use the same type of formula to add values together.
The formula below is not correct but will I hope give you an indication of
what I am trying to do.

=IF(BK106,(BN106+BX106+CH106+CR106),","",")

Hope someone can be of help!

Pat
 
Pat said:
The following formula will return a value(s) based on BK106 being TRUE
If BK106 is FALSE it will return nothing.

=IF(BK106,(SUBSTITUTE(TRIM(BR106&" "&BS106),"",",")),IF(NOT(BK106),"",""))

I want to use the same type of formula to add values together.
The formula below is not correct but will I hope give you an indication of
what I am trying to do.

=IF(BK106,(BN106+BX106+CH106+CR106),","",")

Hope someone can be of help!

Pat
Click to expand...

You don't need to include a test for NOT(BK106). The syntax is simply
=IF(BK106,ValueIfTrue,ValueIfFalse)
You can put values, or functions that return values, in as ValueIfTrue and
ValueIfFalse.

So, I suspect what you want is simply
=IF(BK106,(BN106+BX106+CH106+CR106),"")
If BK106 is TRUE, this will return the answer to (BN106+BX106+CH106+CR106).
If not, it will return "" so you will see nothing.
 
That worked out well from you both.
Now to extend that approach I want to do the following which is not correct:

=IF(BK57,BN57,"")+IF(BU57,BX57,"")+IF(CE57,CH57,"")+IF(CO57,CR57,"")

I want to add BN57, BX57,CH57 & CR57
This will only happen if each formula returns TRUE and this is represented
by BX57,BU57,CE57 & CO57 which will either return TRUE or FALSE or nothing.

regards
Pat
 
Pat said:
That worked out well from you both.
Now to extend that approach I want to do the following which is not correct:

=IF(BK57,BN57,"")+IF(BU57,BX57,"")+IF(CE57,CH57,"")+IF(CO57,CR57,"")

I want to add BN57, BX57,CH57 & CR57
This will only happen if each formula returns TRUE and this is represented
by BX57,BU57,CE57 & CO57 which will either return TRUE or FALSE or nothing.

regards
Pat
Click to expand...

I think you are making life difficult for yourself by creating a problem and
then trying to solve it.

When you have a formula such as
=IF(BK57,BN57,"")
with a number in BN57, you will get either that number or "" which is text.
Now Excel treats numbers and text differently. You can add numbers, but not
text. Hence, your formula above doesn't work unless all four parts return
numbers.

You have thus created a problem. Why do this in the first place? Let the
formulas always return a number so that adding them will work:
=IF(BK57,BN57,0)+IF(BU57,BX57,0)+IF(CE57,CH57,0)+IF(CO57,CR57,0)
or
=(BK57*BN57)+(BU57*BX57)+(CE57*CH57)+(CO57*CR57)

If you want to suppress a zero result, preferably do this to the whole
workbook
Tools > Options > View tab, uncheck "Zero values".
If you must do this to just the formula result, use one overall IF:
=IF((BK57*BN57)+(BU57*BX57)+(CE57*CH57)+(CO57*CR57)=0,"",(BK57*BN57)+(BU57*B
X57)+(CE57*CH57)+(CO57*CR57))
 
I think Paul, with respect, you did not fully understand the problem.

If you look at Frank solution earlier
=IF(BK106,BN106+BX106+CH106+CR106,"")

this works fine.
BK106 is a custom format "saleable";"saleable";"onhold"
The rest of the cell references have a number value.
If BK106 is "saleable" a result will be returned, if BK106 is "onhold" or
empty no result will return.

Now if we strip away BX106, CH106 & CR106 to leave one cell
reference with a number value we are left with

=IF(BK106,BN106,"")

BK106 will still display "saleable", "onhold" or remain empty
BN106 will still contain a number value.

Where does this leave us?

If we take =IF(BK106,BN106,"")
and duplicate it one or more times and change the cell references for each

=IF(BK106,BN106,"") =IF(BU106,BX106,"")

we would then add

BN106 & BX106

based on whether BX106 & BU106
is displaying "saleable"

Pat
 
Back
Top