count if for a criteria

[Dinesh_Excel]

Hi Friends,

I have a data where the count of the objects should sit in various workgroups as per a criteria as per the column header.

Have used pivot tables but,need to get the count of the objects w/o using pivot tables.

Attached sheet for your reference.

 

[bosco_yip]

1] See attached file

2] Beware to remove extra space in cells of F13:F9, F12 and F15:F21

Regards
Bosco

 

[Dinesh_Excel]

thanks Bosco..for your timely help.

Will be aware of those extra spaces

 

[Dinesh_Excel]

Bosco small change ....the Workgroup naming has changed ..how can we adjust the same now.

Appreciate your help

 

[David Evans]

Bosco small change ....the Workgroup naming has changed ..how can we adjust the same now.

Appreciate your help

If all you've done is add a further descriptor to the WG"X" then use LEFT(TRIM(cellref),3) to strip away the extra characters. See attached file.

Thanks to @bosco_yip for doing all the hard work here. He invented the sledgehammer so you can crack walnuts.

Using Excel Tables and summarizing with Pivot Tables will give you a lot less trouble as you change data ranges etc.

 

[Dinesh_Excel]

Thanks David ..but the total in objects against each workgroup has a numbers as per the Pivot but in summary it showing it as Zero.

 

[bosco_yip]

1] Formula in "Objects" Column G is fixed.

3] See attached revised file

Regards
Bosco

 

[Dinesh_Excel]

thanks, Bosco..apprecaite your help