what is the alternative of countifs function in libreoffice?

users
#1

hi,
is there any alternative for "countifs" function in libreoffice (not
"countif")?
say,in one column gender of the participants are written (male/female).in
next column height of the participant is written.now i want to count how
many females have height >=1.55 metre.how to do it in libreoffice?
in ms office 2010,there is this function- "countifs(criterion range
1,criterion 1,criterion range 2,criterion 2)" which could do this.could not
find anything like this in libreoffice.is there any alternative?
thanks in advance,
regards,

#2

Hi,

say,in one column gender of the participants are written (male/female).in
next column height of the participant is written.now i want to count how
many females have height >=1.55 metre.

Use DataPilot. (Chosse: Data → DataPilot → Start). Your problem is
exactly what it was invented for.

Here is the LibreOffice manual:
http://wiki.documentfoundation.org/images/5/52/0308CG3-DataPilot.pdf

:slight_smile:

Stefan

#3

Am 25.01.2012 18:26, Stefan Weigel wrote:

Hi,

say,in one column gender of the participants are written (male/female).in
next column height of the participant is written.now i want to count how
many females have height>=1.55 metre.

+1 for the data pilot.

For single values you may try SUMPRODUCT(condition1;condition2;...)

#4

ok,data pilot works.i tried it earlier also but somehow missed the
'clicking data field and change the function' part.thanks for the link
@stefan.
@andreas, could not understand how to use sumproduct in my case.is it not a
way of multiplication? in my case, i am trying to filter the data.
regards,

#5

Am 26.01.2012 09:42, soumalya ray wrote:

ok,data pilot works.i tried it earlier also but somehow missed the
'clicking data field and change the function' part.thanks for the link
@stefan.
@andreas, could not understand how to use sumproduct in my case.is it not a
way of multiplication? in my case, i am trying to filter the data.
regards,

SUMPRODUCT($A$1:$A$5="this";$B$1:$B$5="that")
A1*B1 => 1 or 0
A2*B2 => 1 or 0
A3*B3 => 1 or 0
A4*B4 => 1 or 0
A5*B5 => 1 or 0

#6

Hi :slight_smile:
That link tries to download the Pdf version but this link lets you see all the chapters and doesn't try to download anything until after you have had a look around at what you might be downloading
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Calc_Guide

I dunno why but some people get furious about links that force a download!  The single chapter is pretty tiny file-size but i guess some people have very restricted limits and of course some people just enjoy getting angry about anything. 
Regards from
Tom :slight_smile:

#7

no problem like that.downloaded and read it.and as i had mentioned
already,missed the "click data field and change function" part
previously.this time,doing this helped to solve my problem.
now i am trying to include the row/column percentage into the data pilot
result directly along with the absolute count.so far, no luck!!
regards,

#8

soumalya ray wrote

now i am trying to include the row/column percentage into the data pilot
result directly along with the absolute count.so far, no luck!!

I guess you just bumped into the LO Pivot Table limitations (compared to MS
Office)
In MS Office you would just add again the same data field Count and choose
to display results as a percentage or add the totals as percentage.

I'm almost sure that you can't do that in the LibreOffice Pivot Tables
(unless there are some undocumented features that I'm not aware of? )

#9

Am 27.01.2012 11:23, Pedro wrote:

I'm almost sure that you can't do that in the LibreOffice Pivot Tables
(unless there are some undocumented features that I'm not aware of? )

You can select multiple functions for the same data field. The function selector is a multi-select list box.
Indeed, you can not add the same function twice this way. The work-around for this is a duplicate of the original field in the source table.

#10

sorry;could not understand the meaning of 'duplicate the field'.could you
explain a bit more please?
regards,

#11

Am 27.01.2012 19:04, soumalya ray wrote:

sorry;could not understand the meaning of 'duplicate the field'.could you
explain a bit more please?
regards,

Create a calculated field with nothing but a reference to the field you need twice.

#12

its really time consuming.is there any other way to achieve this?
regards,

#13

Am 28.01.2012 05:47, soumalya ray wrote:

its really time consuming.is there any other way to achieve this?
regards,

3 seconds.

#14

yeah and then 10 min to check whether i made any mistake or not!
:smiley: :smiley:
regards,

#15

Hi :slight_smile:
That sort of checking gets considerably faster as you begin to find the powers and limitations of the "new thing".  Also as you find faster and easier ways of checking.  It is one reason why people are very resistant to change.
Regards from
Tom :slight_smile:

#16

Am 29.01.2012 08:27, soumalya ray wrote:

yeah and then 10 min to check whether i made any mistake or not!
:smiley: :smiley:
regards,

Since I know the usual navigation keys and fill methods with all the availlable keyboard shortcuts, I am always sure to do the right thing in *any* spreadsheet.
Simply get some old Excel book and learn the basics.

#17

Hi soumalya ray

soumalya ray wrote

yeah and then 10 min to check whether i made any mistake or not!
:smiley: :smiley:

This kind of answers will only cause people to stop helping you. One
solution was given to you 2 days ago, and yet you don't want to waste 10
minutes. That shows how committed you are to solving your problem.

Just read this Manual
http://www.libreoffice.org/get-help/documentation/#cg

In the future think that this is a community and although people are willing
to help, their time is also valuable (for themselves and for others who
really need help)

Take care,
Pedro