[Solved] COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Discuss the spreadsheet application
Post Reply
Paula12140
Posts: 4
Joined: Fri Oct 07, 2011 8:50 pm

[Solved] COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by Paula12140 »

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.
Last edited by MrProgrammer on Sat May 23, 2020 2:53 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice.org 3.3.0, Windows XP Professional
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by MrProgrammer »

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?
No.
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 count with SUMPRODUCT:    =SUMPRODUCT(1stCriteria;2ndCriteria;…)
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).
Ed2
Volunteer
Posts: 169
Joined: Thu Jan 06, 2011 5:53 pm

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by Ed2 »

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.
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.
Apache OpenOffice 3.4.1 on Windows 7
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by acknak »

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
Ed2
Volunteer
Posts: 169
Joined: Thu Jan 06, 2011 5:53 pm

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by Ed2 »

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.
What do these functions do then?

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by Villeroy »

http://office.microsoft.com/en-us/excel ... 47504.aspx

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
mgroenescheij
Volunteer
Posts: 300
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by mgroenescheij »

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.
tibi
Posts: 1
Joined: Fri May 04, 2012 9:02 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by tibi »

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.
OpenOffice 3.4 on Ubuntu 12.04
java developer bhit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by Villeroy »

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
User avatar
FallenAvatar
Posts: 22
Joined: Sat Apr 21, 2012 7:37 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by FallenAvatar »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by Villeroy »

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
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
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by crusader »

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. :?
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by kingfisher »

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.
Apache OpenOffice 4.1.9 on Linux
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by crusader »

kingfisher wrote:BTW, an issue about the absence of the new functions in AOO 3.4 (to be) has been filed.
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!

Viva Libre...
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by acknak »

crusader wrote:... what is ">1" evaluating? ...
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.

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
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Post by crusader »

acknak wrote:In the goofy world of spreadsheets, any text value is greater than any numeric value...
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: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.
An enlightened mortal agrees...
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
Post Reply