[Solved] COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
-
- Posts: 4
- Joined: Fri Oct 07, 2011 8:50 pm
[Solved] COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
Our office just switched from MS Excel 2007 to OpenOffice calc and writer. There have been several issues with users attempting to use the CountIfs, SumIfs, and AverageIfs functionality from Excel 2007. Have equivalents been created for OO v 3.3 yet for any of the above?
I've found some things about SumProduct being used for CountIfs and SumIfs. Does SumProduct successfully replace both of these functions in OpenOffice Calc?
Thanks a bunch.
I've found some things about SumProduct being used for CountIfs and SumIfs. Does SumProduct successfully replace both of these functions in OpenOffice Calc?
Thanks a bunch.
Last edited by MrProgrammer on Sat May 23, 2020 2:53 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
OpenOffice.org 3.3.0, Windows XP Professional
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
No.Paula12140 wrote:There have been several issues with users attempting to use the CountIfs, SumIfs, and AverageIfs functionality from Excel 2007. Have equivalents been created for OO v 3.3 yet for any of the above?
To count with SUMPRODUCT: =SUMPRODUCT(1stCriteria;2ndCriteria;…)Paula12140 wrote:I've found some things about SumProduct being used for CountIfs and SumIfs. Does SumProduct successfully replace both of these functions in OpenOffice Calc?
To sum with SUMPRODUCT: =SUMPRODUCT(1stCriteria;2ndCriteria;…;SumField)
To average with SUMPRODUCT: =SUMPRODUCT(1stCriteria;2ndCriteria;…;SumField)/SUMPRODUCT(1stCriteria;2ndCriteria;…)
There are no doubt numerous examples of the first two in Calc topics.
If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
OOo has both SUMIF and COUNTIF functions. I've never heard of the AVERAGEIF function, but surely you could get the same result with a SUMIF divided by a COUNTIF with the same parameters.Paula12140 wrote:Our office just switched from MS Excel 2007 to OpenOffice calc and writer. There have been several issues with users attempting to use the CountIfs, SumIfs, and AverageIfs functionality from Excel 2007. Have equivalents been created for OO v 3.3 yet for any of the above?
I've found some things about SumProduct being used for CountIfs and SumIfs. Does SumProduct successfully replace both of these functions in OpenOffice Calc?
Thanks a bunch.
Apache OpenOffice 3.4.1 on Windows 7
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
They're not the same functions. MS added the {COUNT,SUM,AVERAGE}IFS functions in Excel 2007 and they have not been implemented in Calc yet.
AOO4/LO5 • Linux • Fedora 23
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
What do these functions do then?acknak wrote:They're not the same functions. MS added the {COUNT,SUM,AVERAGE}IFS functions in Excel 2007 and they have not been implemented in Calc yet.
OOo already has SUMIF and COUNTIF functions. What's the difference, and why OOo's functions not suitable?
Apache OpenOffice 3.4.1 on Windows 7
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
http://office.microsoft.com/en-us/excel ... 47504.aspx
These functions add nothing new. It's about "convenience".
These functions add nothing new. It's about "convenience".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 300
- Joined: Thu Apr 23, 2009 10:19 pm
- Location: Sydney Australia
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
From the release notes of Apache OpenOffice 3.4
ODF Spreadsheet learns new Conditional Functions
Support conditional functions COUNTIFS, SUMIFS, AVERAGEIF and AVERAGEIFS
AOO 4.1.5 on MS Windows 10 Professional & MacOS High Sierra 10.13.5
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
there seems to be a lot off discussion about the sumifs function.
first:
there is a difference between the sumif and the sumifs function.
the sumif function sums a column depending on the value if one other coulmn.
the sumifs function sums a column depending on the values if many other columns.
so with the sumif function one can say:
count the value of column a if the value if of column b is 'yes'.
with the sumifs:
count the value of column a if the value if of column b is 'yes' and the value of column c is 1 and the value of column d is march.
second:
is this implemented or not.
when i open this document:
http://www.openoffice.org/sc/testdocs/f ... ff8_12.xls
on tab function in section 4.6 function supported in oox you will see the sumifs function.
when i open it in OO 3.4 it will give an error. (#MACRO?)
so to me it seems that the SUMIFS functino is an imported function (which is implemented by microsoft in 2007) and is not working in OO.
first:
there is a difference between the sumif and the sumifs function.
the sumif function sums a column depending on the value if one other coulmn.
the sumifs function sums a column depending on the values if many other columns.
so with the sumif function one can say:
count the value of column a if the value if of column b is 'yes'.
with the sumifs:
count the value of column a if the value if of column b is 'yes' and the value of column c is 1 and the value of column d is march.
second:
is this implemented or not.
when i open this document:
http://www.openoffice.org/sc/testdocs/f ... ff8_12.xls
on tab function in section 4.6 function supported in oox you will see the sumifs function.
when i open it in OO 3.4 it will give an error. (#MACRO?)
so to me it seems that the SUMIFS functino is an imported function (which is implemented by microsoft in 2007) and is not working in OO.
OpenOffice 3.4 on Ubuntu 12.04
java developer bhit
java developer bhit
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
No, this function does not exist. Instead you can use the exact same SUMPRODUCT formulas that used to work in earlier versions of Excel and all the other spreadsheet applications.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- FallenAvatar
- Posts: 22
- Joined: Sat Apr 21, 2012 7:37 am
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
It seems to me you can achieve the same effect by nestling logical and computational functions. Just takes a bit more typing.
OpenOffice3.x on WindowsXP/Ubuntu
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
SUMIFS( range ; column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( range * column1>1 * column2<5 )
COUNTIFS( column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( column1>1 * column2<5 )
AVERAGEIFS <==> SUMIFS/COUNTIFS
COUNTIFS( column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( column1>1 * column2<5 )
AVERAGEIFS <==> SUMIFS/COUNTIFS
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
I am trying to get a better understanding of how SUMPRODUCT works as a COUNTIFS equivalent in LO/OO in the example below:
John......4
Tom......
Sam......3
Pat.......5
=SUMPRODUCT(A1:A4>1;B1:B4="") and the correct answer is 1. Given that column A is pure text, what is ">1" evaluating? I experimented and found that I could substitute any number for 1 (including zero and negative numbers) and still get the correct answer.
John......4
Tom......
Sam......3
Pat.......5
=SUMPRODUCT(A1:A4>1;B1:B4="") and the correct answer is 1. Given that column A is pure text, what is ">1" evaluating? I experimented and found that I could substitute any number for 1 (including zero and negative numbers) and still get the correct answer.
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
- kingfisher
- Volunteer
- Posts: 2123
- Joined: Tue Nov 20, 2007 10:53 am
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
Quite strange. I used columns A and B, rows 35 to 38. The following formula displays the contents of A35 ('Mark'): =IF(A35>1;A35;0)
Changing '1' to '11' produced the same result.
BTW, an issue about the absence of the new functions in AOO 3.4 (to be) has been filed.
Changing '1' to '11' produced the same result.
BTW, an issue about the absence of the new functions in AOO 3.4 (to be) has been filed.
Apache OpenOffice 4.1.9 on Linux
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
Thank God: I addressed the absence of the promised functions on another thread -- without a response. Taking (what seems like) forever to come up with an upgrade and then not having the promised features is not nice!kingfisher wrote:BTW, an issue about the absence of the new functions in AOO 3.4 (to be) has been filed.
Viva Libre...
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
In the goofy world of spreadsheets, any text value is greater than any numeric value, so the A1:A4>1 part of your formula is true (1) for any text value in the cells and any numeric constant in the formula. If the range has an empty name, it won't be counted no matter what the value is.crusader wrote:... what is ">1" evaluating? ...
The primary value of the new functions is just clarity, as far as I can see: they're easier to understand and to use. SUMPRODUCT is a "swiss army knife" well-known to spreadsheet wizards, but a rather impenetrable mystery to mere mortals.
AOO4/LO5 • Linux • Fedora 23
Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?
Thank you, Acknak: that is the answer I was looking for! I now understand how and why the formula works, allowing me to employee it effectively.acknak wrote:In the goofy world of spreadsheets, any text value is greater than any numeric value...
An enlightened mortal agrees...acknak wrote:The primary value of the new functions is just clarity, as far as I can see: they're easier to understand and to use. SUMPRODUCT is a "swiss army knife" well-known to spreadsheet wizards, but a rather impenetrable mystery to mere mortals.
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.