[Solved] COUNTIFS multiple criteria

[KatBl]

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?

[robleyd]

Am I using the right function? Am I using it the right way?

Yes, and no The criterion needs to be an expression, not a function. For your case, a regular expression seems to do the job.

Code: Select all

=COUNTIFS(Model;A3;NewFW;"=.*")

Where "=.*" simply means contains one or more characters. Make sure you have the automatic evaluation of regular expression turned on in Tools - Options - OpenOffice Calc - Calculate.

[KatBl]

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:

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		

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?

[robleyd]

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.

[eeigor]

"=.*" simply means contains one or more characters.

0 or more, including blanks, not one. Use + instead of *

[MrProgrammer]

Hi, and welcome to the forum.

Am I using the right function?

No! The correct function when you need to test if cells are empty is SUMPRODUCT. It is more versitile than feeble COUNTIFS.
[Tutorial] The SUMPRODUCT function especially example X06

=COUNTIFS(Model;A3;NewFW;NOT(ISBLANK(NewFW)))

Use =SUMPRODUCT(Model=A3;NOT(ISBLANK(NewFW)))

Here are the first three rows, so you can see data structure:

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.

This works: =COUNTIFS(NewFW;NOT(ISBLANK(NewFW)))

According to the documentation for COUNTIFS, this will not work. I challenge you to attach an example showing that it does work.

Where "=.*" simply means contains one or more characters.

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.

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

[eeigor]

=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 -

[KatBl]

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

[robleyd]

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.

[KatBl]

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

[robleyd]

Maybe this?

Universal thanks!

coopers.jpg (4.73 KiB) Viewed 3741 times

[KatBl]

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.