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

 

[muhammad susanto]

hi Aladin i am so sorry about that, i can't explained as you want with fully cause my english is not good (i am poor in english language)

 

[Aladin Akyurek]

hi Aladin, syukron, but for :

[COLOR=#333333]=COUNTIFS(A2:A8,"?*",B2:B8,"?*",C2:C8,"yes")[/COLOR]

, not work, should be the result : 4 (pc,monitor,rose,glass)

Of course it does not work for it tries a different set of criteria than the one you have in mind...

=SUM(COUNTIFS(B2:B8,"?*",C2:C8,{"yes","no"}))

Did I guess better this time?

 

[muhammad susanto]

thank, Aladin, i have tested but not i want with your last formula the result = 3, should be the result is 4 (pc,monitor,rose,glass), in case the counting target in col.b (property column) with as criteria in col.c ("yes")..

any other ideas?

 

[Aladin Akyurek]

thank, Aladin, i have tested but not i want with your last formula the result = 3, should be the result is 4 (pc,monitor,rose,glass), in case the counting target in col.b (property column) with as criteria in col.c ("yes")..

any other ideas?

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>

Try this one too...

=SUM(COUNTIFS(B2:B8,"?*",C2:C8,{"","yes"}))

 

[muhammad susanto]

still not working, after using your formula the result is 7, i think for blank criteria included calculating (e.g. notebook,lcd,mouse), for blank criteria should be not calculated (assuming only yes criteria is looking for)

i have tried to modify your formula but not work....

 

[Aladin Akyurek]

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

<TBODY>
</TBODY>

Try this one too...

=SUM(COUNTIFS(B2:B8,"?*",C2:C8,{"","yes"}))

still not working, after using your formula the result is 7, i think for blank criteria included calculating (e.g. notebook,lcd,mouse), for blank criteria should be not calculated (assuming only yes criteria is looking for)

i have tried to modify your formula but not work....

Ok, how do you get that 4 yourself?

 

[muhammad susanto]

for "yes" criteria :

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

<tbody>
</tbody>

4 = pc,monitor,rose and glass

 

[Aladin Akyurek]

for "yes" criteria :

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

<TBODY>
</TBODY>

4 = pc,monitor,rose and glass

Are those items are you criteria, that is, we have to look for them in particular?

 

[muhammad susanto]

yes, Aladin..i want you help me to calculate item of property based on "yes" criteria..look like the red color in the column above

 

[Aladin Akyurek]

yes, Aladin..i want you help me to calculate item of property based on "yes" criteria..look like the red color in the column above

No, I'm asking you whether the items pc, monitor, rose, and glass must be used as criteria for the range in column B?