## [Solved] Count adjacent cells that meet specific criteria

### [Solved] Count adjacent cells that meet specific criteria

Hi,
I am trying to count cells that meet the following creteria:

"Count any cell in column A that contains “text 1” and the next cell to the right to it (in column B) contains “text 2”, where "text 1" and "text 2" are specific text data".

Searching for the solution I have found COUNTIF function, but I can't figure out how to "translate" my creteria so Calc would understand me. I believe it's something to do with VLOOKUP and IF/AND operators but I don't have any idea.
Sorry for bothering with what I believe is pretty easy task for any learned user of OpenOffice. I have never turned to Calc for anything more complex than budget spreadsheet.
Last edited by Hagar Delest on Sun Jan 20, 2019 12:12 pm, edited 1 time in total.
Reason: tagged solved
Open Office 4.1.5, Windows 7
taras_frolov

Posts: 2
Joined: Wed Jan 16, 2019 4:33 pm

### Re: Count adjacent cells that meet specific criteria

It is not relevant whether the respective columns are adjacent or not. The formulae below are counting how many pairs of cells meet the criteria.

Assuming the first "SpecificText" is given in cell \$C\$1, and the second ST in cell \$D\$1, you can use (e.g.)
=COUNTIFS(\$A\$2:\$A\$101;\$C\$1;\$B\$2:\$B\$101;\$D\$1) or
=SUMPRODUCT((\$A\$2:\$A\$101=\$C\$1)*(\$B\$2:\$B\$101=\$D\$1)) or
{=COUNTIF((\$A\$2:\$A\$101=\$C\$1)*(\$B\$2:\$B\$101=\$D\$1)=1;TRUE())}
entered for array-evaluation with Ctrl+Shift +Enter. (Don't enter they curly brackets. They are generated by Calc.)

The second and the third formula are given here, because in variant cases (slightly more complicated conditions) the first one may not be applicable. In addition older versions of AOO dont' have the COUNTIFS() function implemented.

(Of course, you can also give the text1, text2 as constant strings in the formula. I would dissuade of.
Last edited by Lupp on Wed Jan 16, 2019 5:25 pm, edited 1 time in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2523
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Count adjacent cells that meet specific criteria

Try the COUNTIFS function. It is similar to the COUNTIF function, but allows multiple conditions.
Please note that the parameter order for the COUNTIFS function differs from that of the COUNTIF function.
Also the COUNTIFS function is not in the help file, but will generate the help balloon when entering it to assist with getting the parameters in the correct order.

Another thought is to use the SUMPRODUCT function, which seems contradictory based on the name of the function, but will work as the function would do a automatic IF for the comparisons, returning 1 for a condition that matches or 0 for one that doesn't match. Then the function takes those 1's and 0's and performs the SUMPRODUCT on them.

Searching the forum for SUMPRODUCT should return you a number of previous topics where it has been suggested as an option, and some of them should show the type of comparison layout you would need using strings in the range.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5301
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Count adjacent cells that meet specific criteria

Dear RusselB and Lupp,
Thank you so much for your help!
COUNTIFS is just what I needed -- works perfectly!
Open Office 4.1.5, Windows 7
taras_frolov

Posts: 2
Joined: Wed Jan 16, 2019 4:33 pm

### Who is online

Users browsing this forum: matt621 and 13 guests