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

 

[tedomedo]

Sorry, Marcelo, your formula sometimes doesn't work. If i search only for 8 in first row which is in columns B, D and G, formula should return 3 but your formula returns 1.

 

[Marcelo Branco]

Sorry, Marcelo, your formula sometimes doesn't work. If i search only for 8 in first row which is in columns B, D and G, formula should return 3 but your formula returns 1.

I think I misunderstood what you want.
I thought you wanted to check if the row contain the number in column J, i.e., if there are one or more "8" in A1:G1 the formula should count just 1 for this row.

See if this does what you need

=SUMPRODUCT(COUNTIF(OFFSET($A$1:$G$5,ROW($A$1:$G$5)-ROW($A$1),0,1),N(OFFSET($J$1:$J$5,ROW($J$1:$J$5)-ROW($J$1),0,1))))

M.

 

[tedomedo]

Control+shift+enter, not just enter:

=IF(SUM(IF(MMULT((IF(ISNUMBER($J$1:$J$5),$A$1:$G$5,"#")=$J$1:$J$5)+0,
  TRANSPOSE(COLUMN($A$1:$G$5)^0))>0,1))=COUNT($J$1:$J$5),COUNT($J$1:$J$5),0)

It seems your formula has errors too.

 

[Aladin Akyurek]

It seems your formula has errors too.

Try to precise your specs...

J-range contains 3 numbers; J1 occurs 3 times in row 1; J3 occurs 3 times in row3; J5 the same pattern; Your presumably working COUNTIFS returns 3. When asked, what happens with J5 = 20 occurring 0 times, the result must be 0 you said...

 

[tedomedo]

I think I misunderstood what you want.
I thought you wanted to check if the row contain the number in column J, i.e., if there are one or more "8" in A1:G1 the formula should count just 1 for this row.

See if this does what you need

=SUMPRODUCT(COUNTIF(OFFSET($A$1:$G$5,ROW($A$1:$G$5)-ROW($A$1),0,1),N(OFFSET($J$1:$J$5,ROW($J$1:$J$5)-ROW($J$1),0,1))))

M.

I do want that formula checks column J, i just say that if i enter 8 in J1 cell, formula has to return 3, because number 8 is in three columns.

 

[Marcelo Branco]

I do want that formula checks column J, i just say that if i enter 8 in J1 cell, formula has to return 3, because number 8 is in three columns.

Ok, I understand now.

See if this simpler version works (using ; semicolon as argument separator)

=SUMPRODUCT((A1:G5=N(OFFSET(J1:J5;ROW(J1:J5)-ROW(J1);0;1)))*(J1:J5<>""))

M.

 

[tedomedo]

Try to precise your specs...

J-range contains 3 numbers; J1 occurs 3 times in row 1; J3 occurs 3 times in row3; J5 the same pattern; Your presumably working COUNTIFS returns 3. When asked, what happens with J5 = 20 occurring 0 times, the result must be 0 you said...

This really drives me nuts. See updated table:

A	B	C	D	E	F	G			J
15	8	20	8	6	3	8
15	5	12	11	17	5	2
13	3	5	2	5	3	2
19	12	1	18	1	15	8
10	17	2	18	2	6	18

<tbody>
</tbody>

If i enter to cell J2 number 5 and to cell J3 number 3, result is 2, formula has to find how many times are both 5 in second row and 3 in third row. As you can see, combination (num. 5 in 2nd row and num. 3 in third row) appears twice.
Next (old) example: if i enter to J1 number 8, to J3 number 2 and to J5 number 18, result is 3, because this combination (8 in 1st row, 3 in 3rd row and 18 in 5th row) appears three times.

 

[Aladin Akyurek]

This really drives me nuts. See updated table:

A	B	C	D	E	F	G			J
15	8	20	8	6	3	8
15	5	12	11	17	5	2
13	3	5	2	5	3	2
19	12	1	18	1	15	8
10	17	2	18	2	6	18

<tbody>
</tbody>

If i enter to cell J2 number 5 and to cell J3 number 3, result is 2, formula has to find how many times are both 5 in second row and 3 in third row. As you can see, combination (num. 5 in 2nd row and num. 3 in third row) appears twice.
Next (old) example: if i enter to J1 number 8, to J3 number 2 and to J5 number 18, result is 3, because this combination (8 in 1st row, 3 in 3rd row and 18 in 5th row) appears three times.

If J-numbers occur in their respective rows equally often, return that occurrence count, otherwise 0. For example, J2 would occur 5 times in row 2, J3 must occur equally often in row 3. The result is 5, otherwise 0. Right?

 

[tedomedo]

Result 0 is if i search for combination of e. g. 4 in 3rd row, 1 in 4th row and 2 in 5th row - such search is performed by entering 4 in J3, 1 in J4 and 2 in J5. Such combination doesn't exist and that's why result is 0.

 

[Marcelo Branco]

If i enter to cell J2 number 5 and to cell J3 number 3, result is 2, formula has to find how many times are both 5 in second row and 3 in third row. As you can see, combination (num. 5 in 2nd row and num. 3 in third row) appears twice.

Next (old) example: if i enter to J1 number 8, to J3 number 2 and to J5 number 18, result is 3, because this combination (8 in 1st row, 3 in 3rd row and 18 in 5th row) appears three times.

To make things easier we can use a helper row (in red in the example below)

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

15​

8​

20​

8​

5​

3​

8​

8​

Result​

2​

15​

5​

12​

11​

17​

5​

2​

3​

3​

13​

2​

5​

2​

4​

3​

2​

2​

4​

19​

11​

1​

18​

11​

15​

8​

5​

10​

18​

2​

18​

13​

6​

18​

18​

6​

7​

8​

9​

10​

0​

3​

0​

3​

0​

0​

3​

<TBODY>
</TBODY>

Formula in A10 copied across till G10
=SUMPRODUCT(--(A1:A5=$J$1:$J$5),--(A1:A5<>""),--($J$1:$J$5<>""))

Formula in L2 (Result)
=IF(COUNT(J1:J5),COUNTIF(A10:G10,COUNT(J1:J5)),"")

M.