[Solved] Count adjacent cells that meet specific criteria

[taras_frolov]

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.

[Lupp]

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.

[RusselB]

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.

[taras_frolov]

Dear RusselB and Lupp,
Thank you so much for your help!
COUNTIFS is just what I needed -- works perfectly!