Counting data using countif/countifs formula

[muhammad susanto]

hi all,

i need some excel formula to solve this below problem. with using COUNTIF/COUNTIFS how to counting data with 3 mode ;

name	property	checking
robert	pc	yes
	monitor
david	keyboard	no
alfie	notebook	(empty cell/blank cell)
	lcd
	mouse
zidan	rose	yes
	glass
etc..

<tbody>
</tbody>

i want to count with criteria based on adjacent value "name" column related with "checking" column
1) counting data "name" with "yes" criteria?
2) counting data "name" with "yes" & "no" criteria?
3) counting data "name" with blank "" criteria?
4) counting data "property" with criteria contains "name" and "yes" criteria

any assistance would be greatly appreciated..

regards

m.susanto

 

[Marcelo Branco]

I suggest you use a Helper column (column D in the example below)

	A​	B​	C​	D​	E​	F​	G​
1​	name​	property​	checking​	HELPER​		criteria​	result (based on "name")​
2​	robert​	pc​	yes​	yes​		yes​	2​
3​		monitor​		yes​		no​	1​
4​	david​	keyboard​	no​	no​		blank​	1​
5​	alfie​	notebook​		blank​
6​		lcd​		blank​		criteria​	result (based on "property")​
7​		mouse​		blank​		yes​	4​
8​	zidan​	rose​	yes​	yes​		no​	1​
9​		glass​		yes​		blank​	3​

<TBODY>
</TBODY>

Formula in D2 (Helper)
=IF(A2<>"",IF(C2<>"",C2,"blank"),D1)

Formula in G2
=COUNTIFS($A$2:$A$9,"?*",$D$2:$D$9,F2)
copy down till G4

Formula in G7
=COUNTIFS($B$2:$B$9,"?*",$D$2:$D$9,F2)
copy down till G9

M.

 

[muhammad susanto]

no, as criteria in column C (yes,no,blank). if criteria in col.c contains "yes" in col. b must be 4 item consists of pc,monitor,rose,glass

 

[Aladin Akyurek]

no, as criteria in column C (yes,no,blank). if criteria in col.c contains "yes" in col. b must be 4 item consists of pc,monitor,rose,glass

name	property	checking
robert	pc	yes
	monitor
david	keyboard	no
alfie	notebook
	lcd
	mouse
zidan	rose	yes
	glass

<COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3384" width=95><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4835" width=136><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3925" width=110><TBODY>
</TBODY>

This is really exaspareting. Column C we have 2 yes items. How do we get 4 we also look at column B?

 

[muhammad susanto]

at the first, i also confused too but reply from Marcelo (OP #21) make me know that in this case we need helper column..
it's exactly that i want but in this case can we do without helper column.It's possible?

 

[muhammad susanto]

hi Marcelo, thanks for your answer..
i am helped...

 

[Aladin Akyurek]

at the first, i also confused too but reply from Marcelo (OP #21) make me know that in this case we need helper column..
it's exactly that i want but in this case can we do without helper column.It's possible?

You still need to answer the question worded multiple times...

http://www.mrexcel.com/forum/excel-...ng-data-using-countif-countifs-formula-2.html

 

[muhammad susanto]

hi Aladin, of course..

why you give me the same link in this forum, have you missing?

 

[Marcelo Branco]

hi Marcelo, thanks for your answer..
i am helped...

You are welcome and thanks for the feedback.

M.

 

[Peter_SSs]

... can we do without helper column.It's possible?

Using standard Excel functions I can't see a feasible way. I don't see a problem with a helper column and it can always be hidden if required.

However, if you really didn't want a helper column, you could consider a user-defined function like this. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formulas as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled file (*.xlsm)

Function CritCount(rCrit As Range, sCrit As String, lBasedOnColumn As Long) As Long
  Dim a
  Dim i As Long, k As Long

  a = rCrit.Value
  For i = 1 To UBound(a, 1)
    If a(i, 3) = vbNullString Then
      If a(i, 1) = vbNullString Then
        a(i, 3) = a(i - 1, 3)
      Else
        a(i, 3) = "blank"
      End If
    End If
    If a(i, lBasedOnColumn) <> vbNullString And a(i, 3) = LCase(sCrit) Then k = k + 1
  Next i
  CritCount = k
End Function

Excel Workbook
	A	B	C	D	E	F	G
1	name	property	checking			criteria	result (based on "name")
2	robert	pc	yes			yes	2
3		monitor				no	1
4	david	keyboard	no			blank	1
5	alfie	notebook
6		lcd				criteria	result (based on "property")
7		mouse				yes	4
8	zidan	rose	yes			no	1
9		glass				blank	3
Count

 

[muhammad susanto]

hi Peter, thanks you for your feedback...

You make a difference, by the way, it's awesome...