Count of specif cell in multiple column

[manoj_arsul]

I want a program for below data

filter to condition1 for use , then condition2 for use & then for condition3 for use

Room No Condition 1 Condition 2 Condition 3
1 Use Use Use
2 Not in Use Use Not in Use
3 Use Use Use
4 Not in Use Use Not in Use
5 Use Not in Use Not in Use
6 Use Not in Use Use
7 Use Use Use
8 Use Use Use
9 Not in Use Not in Use Use
10 Not in Use Use Not in Use



Room No Condition 1 Condition 2 Condition 3
1 Use Use Use
3 Use Use Use
7 Use Use Use
8 Use Use Use


Out put : 4

 

[jtakw]

Hi,

If you're just looking for the count when All 3 Columns are "Use":

Book1
	A	B	C	D
1	Room No	Condition 1	Condition 2	Condition 3
2	1	Use	Use	Use
3	2	Not in Use	Use	Not in Use
4	3	Use	Use	Use
5	4	Not in Use	Use	Not in Use
6	5	Use	Not in Use	Not in Use
7	6	Use	Not in Use	Use
8	7	Use	Use	Use
9	8	Use	Use	Use
10	9	Not in Use	Not in Use	Use
11	10	Not in Use	Use	Not in Use
12
13
14	Output	4
Sheet74

Cell Formulas
Range	Formula
B14	=COUNTIFS(B2:B11,"Use",C2:C11,"Use",D2:D11,"Use")

 

[manoj_arsul]

Cell Count of : filter multiple columns for one value

Hi ,

Can any one send me code for count of below condition . Row data is in sheet2

Filter to column A for "Present" , Filter column B to "Present" , Filter column C to "Present" ,


Row Data Is below:


A B C
Present in AD Present in AV Present in SCCM
Present in AD Present in AV Not Present in SCCM
Present in AD Not Present in AV Not Present in SCCM
Not Present in AD Present in AV Present in SCCM
Present in AD Present in AV Present in SCCM
Not Present in AD Not Present in AV Not Present in SCCM
Present in AD Present in AV Present in SCCM
Present in AD Present in AV Present in SCCM
Present in AD Not Present in AV Not Present in SCCM

filter to present in all these 3 column :


A B C
Present in AD Present in AV Present in SCCM
Present in AD Present in AV Present in SCCM
Present in AD Present in AV Present in SCCM
Present in AD Present in AV Present in SCCM


Out put in next sheet1 in cell A1 = ( ANS is = 4 )

 

[Fluff]

Re: Cell Count of : filter multiple columns for one value

Have a look at the subtotal function http://www.contextures.com/excelfunctionsubtotal.html

 

[jim may]

Re: Cell Count of : filter multiple columns for one value

With your data sheet looking as follows BEFORE processing -- Except Add Column Notation "Select?" in Cell D1
Excel 2010

	A	B	C	D
A	B	C	select?
Present in AD	Present in AV	Present in SCCM
Present in AD	Present in AV	Not Present in SCCM
Present in AD	Not Present in AV	Not Present in SCCM
Not Present in AD	Present in AV	Present in SCCM
Present in AD	Present in AV	Present in SCCM
Not Present in AD	Not Present in AV	Not Present in SCCM
Present in AD	Present in AV	Present in SCCM
Present in AD	Present in AV	Present in SCCM
Present in AD	Not Present in AV	Not Present in SCCM

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

Run the Macro (Below):

Sub Foo()
Range("A1").Select
Range("D2:D10000").ClearContents
Set MySelect = Range("A2:C2")    '**Your Current Mask Selection**
Set MyData = MySelect.CurrentRegion
Set MyData = MyData.Offset(1).Resize(MyData.Rows.Count - 1)
For i = MyData.Row To MyData.Rows.Count + 1
    If Cells(i, 1) = MySelect(1) And _
       Cells(i, 2) = MySelect(2) And _
       Cells(i, 3) = MySelect(3) Then
    Cells(i, 4) = "YES"
    End If
Next i
With Range("A1").CurrentRegion
    .AutoFilter Field:=4, Criteria1:="<>"
End With
End Sub

to Produce:

Excel 2010

	A	B	C	D
A	B	C	select?
Present in AD	Present in AV	Present in SCCM	YES
Present in AD	Present in AV	Present in SCCM	YES
Present in AD	Present in AV	Present in SCCM	YES
Present in AD	Present in AV	Present in SCCM	YES

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

</tbody>

Sheet1​

 

[manoj_arsul]

Re: Cell Count of : filter multiple columns for one value

Hi @Fluff

Sorry , I was not able to elaborate last que .

I want how many rooms having all 3 ( AD , AV , SCCM ) are present

For Data :

A B C
Room No
101 Present in AD Present in AV Present in SCCM
102 Present in AD Present in AV Not Present in SCCM
103 Present in AD Not Present in AV Not Present in SCCM
104 Not Present in AD Present in AV Present in SCCM
105 Present in AD Present in AV Present in SCCM
106 Not Present in AD Not Present in AV Not Present in SCCM
107 Present in AD Present in AV Present in SCCM
108 Present in AD Present in AV Present in SCCM
109 Present in AD Not Present in AV Not Present in SCCM

After filter 3 columns like A - Present in AD B-Present in AV C-Present in SCCM

Room No A B C
101 Present in AD Present in AV Present in SCCM
105 Present in AD Present in AV Present in SCCM
107 Present in AD Present in AV Present in SCCM
108 Present in AD Present in AV Present in SCCM

Out Put (ANS) = First column Count 4

 

[manoj_arsul]

Re: Cell Count of : filter multiple columns for one value

With your data sheet looking as follows BEFORE processing -- Except Add Column Notation "Select?" in Cell D1
Excel 2010

	A	B	C	D
A	B	C	select?
Present in AD	Present in AV	Present in SCCM
Present in AD	Present in AV	Not Present in SCCM
Present in AD	Not Present in AV	Not Present in SCCM
Not Present in AD	Present in AV	Present in SCCM
Present in AD	Present in AV	Present in SCCM
Not Present in AD	Not Present in AV	Not Present in SCCM
Present in AD	Present in AV	Present in SCCM
Present in AD	Present in AV	Present in SCCM
Present in AD	Not Present in AV	Not Present in SCCM

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

Run the Macro (Below):

Sub Foo()
Range("A1").Select
Range("D2:D10000").ClearContents
Set MySelect = Range("A2:C2")    '**Your Current Mask Selection**
Set MyData = MySelect.CurrentRegion
Set MyData = MyData.Offset(1).Resize(MyData.Rows.Count - 1)
For i = MyData.Row To MyData.Rows.Count + 1
    If Cells(i, 1) = MySelect(1) And _
       Cells(i, 2) = MySelect(2) And _
       Cells(i, 3) = MySelect(3) Then
    Cells(i, 4) = "YES"
    End If
Next i
With Range("A1").CurrentRegion
    .AutoFilter Field:=4, Criteria1:="<>"
End With
End Sub

to Produce:

Excel 2010

	A	B	C	D
A	B	C	select?
Present in AD	Present in AV	Present in SCCM	YES
Present in AD	Present in AV	Present in SCCM	YES
Present in AD	Present in AV	Present in SCCM	YES
Present in AD	Present in AV	Present in SCCM	YES

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]6
[/TD]

[TD="align: center"]8
[/TD]

[TD="align: center"]9[/TD]

</tbody>

Sheet1
​

Thank u @ jim may
Program is also running right but I want only for Present . Your program is running (selected) for both Present & Not

 

[Fluff]

Re: Cell Count of : filter multiple columns for one value

@manoj_arsul
In future please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.

I have merged both threads