[Solved] Count adjacent cells that meet specific criteria

Discuss the spreadsheet application

[Solved] Count adjacent cells that meet specific criteria

Postby taras_frolov » Wed Jan 16, 2019 4:51 pm

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.
I'll be gratefull for any of your advise or suggestion.
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

Postby Lupp » Wed Jan 16, 2019 5:20 pm

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.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2307
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Count adjacent cells that meet specific criteria

Postby RusselB » Wed Jan 16, 2019 5:22 pm

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
Volunteer
 
Posts: 4827
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Count adjacent cells that meet specific criteria

Postby taras_frolov » Wed Jan 16, 2019 10:47 pm

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 33 guests