Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 15 Dec 2013 17:40:52 +0100LO Spreadsheet: If cell-range contains X, then do Yhttps://ask.libreoffice.org/en/question/27099/lo-spreadsheet-if-cell-range-contains-x-then-do-y/I'm unsure how to do this.
Task: If e.g. cell B12 to B41 contains value "1", then and only then, create SUM of cells C12 to C41 with the value they have (but only for the rows in which the B-column does have a value "1") and enter that value into target cell C 60.
Maybe I better describe what I'm trying to do here: I keep track of something in a table that has colums Mo-So for the weekdays in which I enter a 1 if the event is on that day. Next to that I have columns going from 9-24 which is the hour of the day. If a certain even happens I enter a "1" for that hour of the day.
What I now want is some formula that does the following: Create a nice weekly table for all weekdays and add events at the correct hour they happened.
I'm unsure how to do this. Guess it's possible but the formula needed makes me dizzy and I don't know where to start.Fri, 13 Dec 2013 15:28:26 +0100https://ask.libreoffice.org/en/question/27099/lo-spreadsheet-if-cell-range-contains-x-then-do-y/Answer by Rugslug for <p>I'm unsure how to do this.</p>
<p>Task: If e.g. cell B12 to B41 contains value "1", then and only then, create SUM of cells C12 to C41 with the value they have (but only for the rows in which the B-column does have a value "1") and enter that value into target cell C 60.</p>
<p>Maybe I better describe what I'm trying to do here: I keep track of something in a table that has colums Mo-So for the weekdays in which I enter a 1 if the event is on that day. Next to that I have columns going from 9-24 which is the hour of the day. If a certain even happens I enter a "1" for that hour of the day.</p>
<p>What I now want is some formula that does the following: Create a nice weekly table for all weekdays and add events at the correct hour they happened.</p>
<p>I'm unsure how to do this. Guess it's possible but the formula needed makes me dizzy and I don't know where to start.</p>
https://ask.libreoffice.org/en/question/27099/lo-spreadsheet-if-cell-range-contains-x-then-do-y/?answer=27101#post-id-27101Hi,
Try using either SUMIF or SUMIFS. SUMIFS allows for multiple criteria.
In cell C60, =SUMIF(B12:B41,"=1",C12:C41)
This formula will look at B12 to B41, checks to see if there is a 1 (use the quotes even for numerical values), then adds up the corresponding value in the range C12 to C41.
Tim
Fri, 13 Dec 2013 16:51:06 +0100https://ask.libreoffice.org/en/question/27099/lo-spreadsheet-if-cell-range-contains-x-then-do-y/?answer=27101#post-id-27101Comment by nighthome for <p>Hi,</p>
<p>Try using either SUMIF or SUMIFS. SUMIFS allows for multiple criteria.</p>
<p>In cell C60, =SUMIF(B12:B41,"=1",C12:C41)</p>
<p>This formula will look at B12 to B41, checks to see if there is a 1 (use the quotes even for numerical values), then adds up the corresponding value in the range C12 to C41.</p>
<p>Tim</p>
https://ask.libreoffice.org/en/question/27099/lo-spreadsheet-if-cell-range-contains-x-then-do-y/?comment=27163#post-id-27163Thanks, that works. Seems I can't mark your answer as "solved my issue" or it's in my blind spot.Sun, 15 Dec 2013 17:40:52 +0100https://ask.libreoffice.org/en/question/27099/lo-spreadsheet-if-cell-range-contains-x-then-do-y/?comment=27163#post-id-27163