Help with COUNTIFS on multiple conditons

E

edwardtong694

Board Regular
Joined
Aug 21, 2009
Messages
125
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 - high4 - 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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Try this formula

=SUMPRODUCT(ISNUMBER(MATCH(A2:A100,{"Active","Tolerated"},0)*MATCH(LEFT(B2:B100),{"3","4"},0)*MATCH(LEFT(C2:C100),{"2","3"},0))+0)
 
Upvote 0
Let me ask here something about COUNTIFS on changeable search range. This is an example:

ABCDEFGJ
1582085388
15512111752
132524322
191111811158
10182181361818

<tbody>
</tbody>

I enter in column J what do i want to find in range from A to G columns, but also in which rows. In this example i want to find if there is number 8 in first row, number 2 in 3rd row and number 18 in 5th row. I know i can solve this by using formula =COUNTIFS(A1:G1;J1;A3:G3;J3;A5:G5;J5).
Problem is that i don't want to change this formula manually each time when i change rows search range - let's say that my next search is for number 2 in second row and number 11 in 4th row, consequently i delete previous J column's entries and enter 2 in J2 cell and 11 in J4 cell. How should formula looks like?
Thanks.
 
Last edited:
Upvote 0
tedomedo said:
Let me ask here something about COUNTIFS on changeable search range. This is an example:

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

<TBODY>
</TBODY>

I enter in column J what do i want to find in range from A to G columns, but also in which rows. In this example i want to find if there is number 8 in first row, number 2 in 3rd row and number 18 in 5th row. I know i can solve this by using formula =COUNTIFS(A1:G1;J1;A3:G3;J3;A5:G5;J5).
Problem is that i don't want to change this formula manually each time when i change rows search range - let's say that my next search is for number 2 in second row and number 11 in 4th row, consequently i delete previous J column's entries and enter 2 in J2 cell and 11 in J4 cell. How should formula looks like?
Thanks.
Click to expand...


Hi tedomedo,

Welcome to Mr Excel Forum

It is always better create a new thread.

Anyway, see if this helps

=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)))>0))

M.
 
Last edited:
Upvote 0
Marcelo, my Office 2007 say that there's an error in first offset part of your formula.
Edit, i just fixed it, change "," to ";" and it seems it works.
 
Last edited:
Upvote 0
tedomedo said:
Result would be 0.
Click to expand...

Presumably, otherwise 3, right? If so:

One way...

Control+shift+enter, not just enter:
Rich (BB code): 
=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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Conditional Formating - Dynamic "not between"
Replies
3
Views
244
dreid1011
dreid1011
J
  • Question
index and Countif
Replies
2
Views
171
Fluff
Fluff
P
  • Question
Return a value from a table that is in the top n percent of a person's trial scores
2
Replies
13
Views
392
Fluff
Fluff
A
  • Question
Macros for cleaning excel file
2
Replies
18
Views
394
jolivanes
jolivanes
S
  • Locked
  • Question
Sampling of data using macros
Replies
1
Views
210
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,620
Messages
6,120,554
Members
448,970
Latest member
kennimack

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top