- **Excel Worksheet Functions**
(*https://www.excelbanter.com/excel-worksheet-functions/*)

- - **IF and SUM**
(*https://www.excelbanter.com/excel-worksheet-functions/26502-if-sum.html*)

IF and SUMHow do I say
If the sum of A1:E1 is greater than 1, enter a 1 in the column, but if not then enter a 0? |

One way:
=IF(SUM(A1:E1)1,1,0) or, more compactly (using the fact that XL coerces TRUE/FALSE to 1/0 in math operations): =--(SUM(A1:E1)1) where the first - coerces TRUE/FALSE to -1/0, and the second back to 1/0. In article , "Natalie" wrote: How do I say If the sum of A1:E1 is greater than 1, enter a 1 in the column, but if not then enter a 0? |

Thanks
What if I wanted to count the number of SS or LS in these cells? "JE McGimpsey" wrote: One way: =IF(SUM(A1:E1)1,1,0) or, more compactly (using the fact that XL coerces TRUE/FALSE to 1/0 in math operations): =--(SUM(A1:E1)1) where the first - coerces TRUE/FALSE to -1/0, and the second back to 1/0. In article , "Natalie" wrote: How do I say If the sum of A1:E1 is greater than 1, enter a 1 in the column, but if not then enter a 0? |

One way:
=COUNTIF(A1:E1,"SS") In article , "Natalie" wrote: What if I wanted to count the number of SS or LS in these cells? |

Sorry, I still can't make it work -
I want to add all the SS and LS together, and if they are more than one, have a 1 appear, and if the answer is 0 then a 0 appear? Thanks for all your help "JE McGimpsey" wrote: One way: =COUNTIF(A1:E1,"SS") In article , "Natalie" wrote: What if I wanted to count the number of SS or LS in these cells? |

One way:
=IF(SUM(COUNTIF(A1:E1,{"SS","LS"})) 0, 1, 0) or more compactly: =--(SUM(COUNTIF(A1:E1,{"SS","LS"})) 0) In article , "Natalie" wrote: Sorry, I still can't make it work - I want to add all the SS and LS together, and if they are more than one, have a 1 appear, and if the answer is 0 then a 0 appear? Thanks for all your help "JE McGimpsey" wrote: One way: =COUNTIF(A1:E1,"SS") In article , "Natalie" wrote: What if I wanted to count the number of SS or LS in these cells? |

All times are GMT +1. The time now is 11:15 AM. |

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

ExcelBanter.com