Countif with or condition plus adding another criteria.

[tomas]

Hi

In cell g4 you can see my formula which count number of occurances of defined combinations for values in b and c column. ( I found a formula and adjusted to my need). But I failed to add another condition that value in column A should be equal to values in my F column.

Please can you help to expand/

regards

 

[rahulshewale1]

Hii,

see if this works?

Formula in Cell G4
=COUNTIFS($A$2:$A$9493,F4,$B$2:$B$9493,">"&0)

Regard
Rahul shewale

 

[tomas]

Hi

No the formula I put so far follows the logic that it counts row if value in b or c column is not equal 0. For this formula works but not sure how to expand to add class condition.

I know easy solution is calculated column but would prefer not to do that

 

[NARAYANK991]

Hi ,

Does this not work ?

=SUM(COUNTIFS($A$10:$A$100008, F4,'raw 1'!$B$10:$B$100008,{">",">","="}&0,'raw 1'!$C$10:$C$100008,{"<","=","<"}&0))

where F4 contains the text vip1.

Copy this down.

Narayan

 

[tomas]

Yes it does I did not check this most obvious option

 

[Juniad]

can u please explain me why there is two time "<" & ">" in criteria s

 

[NARAYANK991]

can u please explain me why there is two time "<" & ">" in criteria s

Hi ,

That is because that is how the OP wants to get the count.

He wishes to apply the following 3 criteria one after the other , and add all 3 counts to get the final output.

First , let us understand that the data in columns B and C are as follows :

1. The data in column B is either 0 or greater than 0.

2. The data in column C is either 0 or less than 0.

The criteria are :

1. Count all those cells which have column B greater than 0 AND column C less than 0. Let us call this COUNT1. You can see from the data that this value is 621.

2. Count all those cells which have column B greater than 0 AND column C equal to 0. Let us call this COUNT2. You can see from the data that this value is 6058.

3. Count all those cells which have column B equal to 0 AND column C less than 0. Let us call this COUNT3. You can see from the data that this value is 94.

The overall count , the final output , is therefore :

COUNT1 + COUNT2 + COUNT3 which equals 6773.

Of course , this could have been simplified to using just 2 criteria , by combining either COUNT1 and COUNT2 or COUNT1 and COUNT3. The two counts would be :

Combining COUNT1 and COUNT2

1. Count all those cells which have column B greater than 0 AND column C less than or equal to 0. Let us call this COUNT12. You can see from the data that this value would be 6679.

3. Count all those cells which have column B equal to 0 AND column C less than 0. Let us call this COUNT3. You can see from the data that this value is 94.

Thus , the final output would be :

COUNT12 + COUNT3 which would equal 6773.


Combining COUNT1 and COUNT3

1. Count all those cells which have column B greater than or equal to 0 AND column C less than 0. Let us call this COUNT13. You can see from the data that this value would be 715.

2. Count all those cells which have column B greater than 0 AND column C equal to 0. Let us call this COUNT2. You can see from the data that this value is 6058.

Thus , the final output would be :

COUNT13 + COUNT2 which would equal 6773.

Narayan