COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Discuss the spreadsheet application

COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby Paula12140 » Thu Mar 15, 2012 9:09 pm

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.
OpenOffice.org 3.3.0, Windows XP Professional
Paula12140
 
Posts: 4
Joined: Fri Oct 07, 2011 8:50 pm

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby MrProgrammer » Thu Mar 15, 2012 9:44 pm

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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1743
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby Ed2 » Thu Mar 15, 2012 10:18 pm

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

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby acknak » Thu Mar 15, 2012 11:38 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17400
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby Ed2 » Fri Mar 16, 2012 12:06 am

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

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby Villeroy » Fri Mar 16, 2012 12:29 am

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17293
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby mgroenescheij » Fri Mar 16, 2012 12:15 pm

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.0.1 on MS Windows 7
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
mgroenescheij
Volunteer
 
Posts: 174
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby tibi » Fri May 04, 2012 9:14 am

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:
www.openoffice.org/sc/testdocs/formula_ ... 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
tibi
 
Posts: 1
Joined: Fri May 04, 2012 9:02 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby Villeroy » Fri May 04, 2012 2:51 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17293
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby FallenAvatar » Fri May 04, 2012 7:56 pm

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

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby Villeroy » Fri May 04, 2012 9:40 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17293
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby crusader » Sat May 05, 2012 4:42 am

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 4.2 :) on Vista :(
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 326
Joined: Sun Jan 20, 2008 5:06 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby kingfisher » Sat May 05, 2012 6:12 am

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.
OpenOffice 3.3 on PCLinuxOS. There are 3 kinds of people: those who can count and those who can't.
User avatar
kingfisher
Volunteer
 
Posts: 1861
Joined: Tue Nov 20, 2007 10:53 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby crusader » Sat May 05, 2012 1:49 pm

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 4.2 :) on Vista :(
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 326
Joined: Sun Jan 20, 2008 5:06 am

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby acknak » Sat May 05, 2012 4:29 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17400
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: COUNTIFS, SUMIFS, AVERAGEIFS Equivalents?

Postby crusader » Sat May 05, 2012 5:22 pm

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 4.2 :) on Vista :(
A candle loses nothing by lighting another candle.
crusader
Volunteer
 
Posts: 326
Joined: Sun Jan 20, 2008 5:06 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests