| View previous topic :: View next topic |
| Author |
Message |
Grandis
OOo Advocate
Joined: 05 Mar 2007
Posts: 232
Location: Norway
|
 Posted: Wed May 12, 2010 7:11 am Post subject: Sumif() - finding text is criteria for sum |
 |
|
Hi.
I want to sum numbers from range C3:C24, but only sum the cells where the cell in same row in range F3:F24 contains the text "1600". The range F3:F24 is formatted as text, not numbers.
I can insert another auxiliary coloumn that is given either 0 or 1, dependent if adjaccent cell in the C coloumn contains "1600" or not, and then used this coloumn as reference when I use the SUMIF command.
The aux coloumn would have this formula (not properly tested yet):
| Code: | | =IF(ISERROR(SEARCH("1600";D3;1));0;1) |
I tried to include the formula above in the sumif command, but I found that it's not the right approach as sumif command deals with ranges, not single cells.
I want to know if I can do this without adding an extra coloumn.
Thanks
_________________
Windows XP sp2 |
|
| Back to top |
|
 |
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Wed May 12, 2010 12:17 pm Post subject: |
|
|
Sorry, I don't get what's wrong with:
| Code: | | =SUMIF(F3:F24;"1600";C3:C24) |
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
Grandis
OOo Advocate
Joined: 05 Mar 2007
Posts: 232
Location: Norway
|
| Posted: Wed May 12, 2010 12:33 pm Post subject: |
|
|
| Robert Tucker wrote: | Sorry, I don't get what's wrong with:
| Code: | | =SUMIF(F3:F24;"1600";C3:C24) |
|
The string "1600" is a part of more surrounding text. So no, it's not that simple. A cell could contains something like "AB1600CD".
_________________
Windows XP sp2 |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Wed May 12, 2010 12:41 pm Post subject: |
|
|
So:
| Code: | | =SUMIF(F3:F24;".*1600.*";C3:C24) |
with "Enable regular expressions in formulas" checked at Tools>Options>OpenOffice.org Calc>Calculate ?
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Wed May 12, 2010 12:44 pm Post subject: |
|
|
Or:
| Code: | | =SUMIF(F3:F24;"1600";C3:C24) |
with "Search criteria = and<> must apply to whole cells" unchecked at Tools>Options>OpenOffice.org Calc>Calculate ?
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
|
