Help with COUNTIFS on multiple conditons

[edwardtong694]

Hi Guys,

Hoping someone out there can help.

I am currently trying to create a Formula which will count my data on a range of conditions.

Here is an example of my data:

	A	B	C
1	Active	4 - High	3 - Medium
2	Closed	2 - low	2 - low
3	Tolerated	3 - Medium	1 - V. Low
4	Active	4 - high	4 - High

<tbody>
</tbody>

This data then continues on for a number of rows. Basically I want to count the cells which contain "Active" or "Tolerated" in row A which also has a score of 4 or 3 in column B and also a score of column 3 or 2 in column C. So the formula should count Rows 1 and 4 in the above scenario.

The problem I have is the data in B and C contains text as well as a number so I assume there is not a way to use a greater or lesser than operater? to help and instead I will need to add a condition to just search the cell which contains the number 4 or 3 in column B for example.

I have had a go at this but am not getting very far. Would COUNTIFS be the best formula to use for this?

Thanks in advance.

Ed

 

[Marcelo Branco]

Without a helper row, maybe this formula

=SUMPRODUCT(--(MMULT(COLUMN(A:E)^0,(A1:G5<>"")*(A1:G5=N(OFFSET(J1:J5,ROW(J1:J5)-ROW(J1),0,1)))*(J1:J5<>""))=COUNT(J1:J5)))

M.

 

[tedomedo]

Muito bem, Marcelo, it works, it seems your second formula without helper row too! Off-topic: Marcelo, contact your football selecao and tell him that Brazil must beat Croatia with at least 2-0 in championship

 

[tedomedo]

Formula without helper row works but if there's nothing in J column, result is 7 - but this isn't problem, J column will always contain something. Thanks again.

 

[tedomedo]

Me again, sorry for another bothering you. My real working table data is big from F3 to EO13 (140 columns, 11 rows), i enter searched combinations to FN column, so that's why i changed your formula without helper row to
=SUMPRODUCT(--(MMULT(COLUMN(F:P)^0;(F3:EO13<>"")*(F3:EO13=N(OFFSET(FN3:FN7;ROW(FN3:FN7)-ROW(FN3);0;1)))*(FN3:FN7<>""))=COUNT(FN3:FN7)))
but my change (moving and resizing ranges in formula) is incorrect, i got #N/A error message. Where are my mistakes?

 

[tedomedo]

Moderator, delete this and my previous post - i solved my mistake.

 

[Marcelo Branco]

Muito bem, Marcelo, it works, it seems your second formula without helper row too! Off-topic: Marcelo, contact your football selecao and tell him that Brazil must beat Croatia with at least 2-0 in championship

Glad that it's working now.

M.
ps: about Brazil x Croatia: I'll talk with Neymar

 

[tedomedo]

Me again asking something related to previous stuff. Let's say that i search using marcelo's formula for value 20 (and other values in many rows) but formula returns result 0. Then, i'd like to expand search, from e. g. 18 to 22 (10% less and more) in order to find something close to 20. Let's say i enter direct value to cell J1, then i enter percent of value expanding to cell J2. How should this formula look like, Marcelo (and other teachers here)?