[Solved] COUNTIFS multiple criteria
[Solved] COUNTIFS multiple criteria
Hi!
This works: =COUNTIFS(Model;A3)
This works: =COUNTIFS(NewFW;NOT(ISBLANK(NewFW)))
When combined like this, doesn't work:=COUNTIFS(Model;A3;NewFW;NOT(ISBLANK(NewFW)))
What am I missing?
Background: we have tabular data in a sheet called Raw_Data, each row represents one object. I want to count each row/object based on two criteria, the model name and whether or not a particular field is not blank.
Am I using the right function? Am I using it the right way?
This works: =COUNTIFS(Model;A3)
This works: =COUNTIFS(NewFW;NOT(ISBLANK(NewFW)))
When combined like this, doesn't work:=COUNTIFS(Model;A3;NewFW;NOT(ISBLANK(NewFW)))
What am I missing?
Background: we have tabular data in a sheet called Raw_Data, each row represents one object. I want to count each row/object based on two criteria, the model name and whether or not a particular field is not blank.
Am I using the right function? Am I using it the right way?
Last edited by KatBl on Mon Aug 02, 2021 6:40 am, edited 1 time in total.
Open Office 4.1.10 (32 bit)
Windows 10 64bit
Windows 10 64bit
Re: COUNTIFS multiple criteria
Yes, and no The criterion needs to be an expression, not a function. For your case, a regular expression seems to do the job.Am I using the right function? Am I using it the right way?
Code: Select all
=COUNTIFS(Model;A3;NewFW;"=.*")
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: COUNTIFS multiple criteria
Thank you for taking the time out to answer me.
Upon copy and paste, I get: Err:502
Trialling just the "=.*" section, it returns 0, when I think it should return at least something different. I have some test data I am using as seen below.
Here are the first three rows, so you can see data structure:
In Sheet called Raw_Data:
In my function, I use the defined name Model which is defined as:
$Raw_Data.$B$1:$B$64348
I also new NewFW which is also a defined name, defined as $Raw_Data.$Y1:$Y$64348
I am running the calculations on a different sheet.
Any ideas?
Upon copy and paste, I get: Err:502
Trialling just the "=.*" section, it returns 0, when I think it should return at least something different. I have some test data I am using as seen below.
Here are the first three rows, so you can see data structure:
In Sheet called Raw_Data:
Code: Select all
Reclamation number Model Type Serial number Warranty 3 Rs-C, R O Date Received Date Repaired Days in shop State of sale Serviced Calibration (repair) Calibration(SERVICE) Casing Replaced Battery Replaced Sensor Replaced Other Part Replaced Adjust Part Soldering Modified Cable Update Beyond Repair Intermittent Rotation Old firmware version New firmware version
7094 T-TEC-7 3-PA A-0000 N C 01/06/20 02/14/20 39 ALT Y N M N Y Y N N N N N N N 1.00 2.00
7094 T-TEC-7 3-PA A-00000 N C 01/06/20 02/14/20 39 ALT Y N Y N N Y N N N N N N N 4
7095 T-TEC-6 1E A-0000 N C 01/09/20 01/17/20 8 ALT Y N Y N N N N N N N N N N
$Raw_Data.$B$1:$B$64348
I also new NewFW which is also a defined name, defined as $Raw_Data.$Y1:$Y$64348
I am running the calculations on a different sheet.
Any ideas?
Last edited by robleyd on Fri Jul 30, 2021 10:43 am, edited 1 time in total.
Reason: Add CODE tags for readability
Reason: Add CODE tags for readability
Open Office 4.1.10 (32 bit)
Windows 10 64bit
Windows 10 64bit
Re: COUNTIFS multiple criteria
Attached is a sample spreadsheet, using both the data I cobbled together to test on Sheet1 and your sample data on Sheet2. The formula querying your data is also on Sheet1.
Without seeing your actual file, I have no way of determining where your problem lies.
Without seeing your actual file, I have no way of determining where your problem lies.
- Attachments
-
- countifs.ods
- (9.37 KiB) Downloaded 161 times
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: COUNTIFS multiple criteria
0 or more, including blanks, not one. Use + instead of *robleyd wrote:"=.*" simply means contains one or more characters.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: COUNTIFS multiple criteria
Hi, and welcome to the forum.
[Tutorial] The SUMPRODUCT function especially example X06
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know
No! The correct function when you need to test if cells are empty is SUMPRODUCT. It is more versitile than feeble COUNTIFS.KatBl wrote:Am I using the right function?
[Tutorial] The SUMPRODUCT function especially example X06
Use =SUMPRODUCT(Model=A3;NOT(ISBLANK(NewFW)))KatBl wrote:=COUNTIFS(Model;A3;NewFW;NOT(ISBLANK(NewFW)))
This is almost useless for us since it is difficult to tell which data matches which heading. It would take many minutes for me study your multi-word headings to import your data into a real spreadsheet for testing, so I am going to skip that and not test my answer with your data. In the future, attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). That is easier for both of us. I would have tested if you had attached.KatBl wrote:Here are the first three rows, so you can see data structure:
According to the documentation for COUNTIFS, this will not work. I challenge you to attach an example showing that it does work.KatBl wrote:This works: =COUNTIFS(NewFW;NOT(ISBLANK(NewFW)))
robleyd no doubt intends to say "zero or more", however since COUNTIFS ignores empty cells the effect of ".+" (one or more) and ".*" (zero or more) is the same. The "=" at the beginning of the criteria is optional.robleyd wrote:Where "=.*" simply means contains one or more characters.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know
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 multiple criteria
=COUNTIFS(Model;A3;NewFW;"<>")
Not tested."<>" means that the criterion is not equal to zero length string (omitted).
It’s the same:
=SUMPRODUCT(Model=A3;NOT(ISBLANK(NewFW))) 'LO Calc
=SUMPRODUCT(Model=A3;NOT(ISBLANK(NewFW))*1) 'Excel: *1 or add double -
Not tested."<>" means that the criterion is not equal to zero length string (omitted).
It’s the same:
=SUMPRODUCT(Model=A3;NOT(ISBLANK(NewFW))) 'LO Calc
=SUMPRODUCT(Model=A3;NOT(ISBLANK(NewFW))*1) 'Excel: *1 or add double -
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Re: COUNTIFS multiple criteria
Thank you so much to you all for answering.
I updated my version of OpenOffice to what I think is the latest (?) version: 4.1.10
I created a new spreadsheet and copied the data across. Maybe there is some dross that is holding the calculations back?
I have triple-checked that using regular expressions is enabled.
I opened robleyd's sample spreadsheet (is there any way I can give them kudos for that?) and had a look. They had it working!!! This means my software should fundamentally be able to do this too since it is the same software. However, copy and paste does not solve this problem.
I have created a test version with some data (some of the data is deleted but is not referred to in the formulae). I have attached it to this post. Sadly, the link for it is hidden beneath the screen shot. It is there, I promise!
Below the data, on one sheet, I have tried both COUNTIFS and SUMPRODUCT. COUNTIFS leads to Err:502 and SUMPRODUCT leads to #VALUE!. I can use both COUNTIFS and SUMPRODUCT on single criteria. I just can't combine them. It would be so useful to combine them.
On the off-chance that this file works for others but not me, I'm providing a screen shot of what I can see with the errors (just so you believe me that I am seeing errors).
Is there some other setting hidden somewhere?
Kat
I updated my version of OpenOffice to what I think is the latest (?) version: 4.1.10
I created a new spreadsheet and copied the data across. Maybe there is some dross that is holding the calculations back?
I have triple-checked that using regular expressions is enabled.
I opened robleyd's sample spreadsheet (is there any way I can give them kudos for that?) and had a look. They had it working!!! This means my software should fundamentally be able to do this too since it is the same software. However, copy and paste does not solve this problem.
I have created a test version with some data (some of the data is deleted but is not referred to in the formulae). I have attached it to this post. Sadly, the link for it is hidden beneath the screen shot. It is there, I promise!
Below the data, on one sheet, I have tried both COUNTIFS and SUMPRODUCT. COUNTIFS leads to Err:502 and SUMPRODUCT leads to #VALUE!. I can use both COUNTIFS and SUMPRODUCT on single criteria. I just can't combine them. It would be so useful to combine them.
On the off-chance that this file works for others but not me, I'm providing a screen shot of what I can see with the errors (just so you believe me that I am seeing errors).
Is there some other setting hidden somewhere?
Kat
- Attachments
-
- countifs-test.ods
- (12.02 KiB) Downloaded 136 times
Open Office 4.1.10 (32 bit)
Windows 10 64bit
Windows 10 64bit
Re: COUNTIFS multiple criteria
Your ranges Model and NewFW have different dimensions and sizes: Model is defined as $Sheet1.$B$2:$B$53 where NewFW is $Sheet1.$Y$2:$Y$52. Change Model to be the same size as NewFW, i.e. $Sheet1.$B$2:$B$52 and the errors go away.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: COUNTIFS multiple criteria
robleyd, you are an absolute star!
Thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you.
Can I give you credit? Kudos? Vote you up? Something?
Thanks you again,
Kat
Thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you.
Can I give you credit? Kudos? Vote you up? Something?
Thanks you again,
Kat
Open Office 4.1.10 (32 bit)
Windows 10 64bit
Windows 10 64bit
Re: COUNTIFS multiple criteria
Maybe this?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: [SOLVED]COUNTIFS multiple criteria
For anyone else coming after, don't modify your names. Simply go to Insert > Names > Define and delete them. (Yes, I know there is a Modify button there. Using that will mess up all your other formulae that rely on the named range.)
Then re-create them.
Fastest way to solve your named range issue.
Then re-create them.
Fastest way to solve your named range issue.
Open Office 4.1.10 (32 bit)
Windows 10 64bit
Windows 10 64bit