Excel data extract/report 2

[nasar]

I have the following data and would like to identify all instances where there is a "NONE" entry for a month.

[b]Month	Response[/b]
Jan	01-Oct
Feb	NONE
Mar	NONE
Apr	02-Oct
May	03-Oct
Jun	01-Oct
Jul	NONE
Aug	04-Oct
Sep	05-Oct
Oct	NONE
Nov	12-Oct
Dec	01-Oct
Jan	02-Oct
Feb	01-Oct
Mar	04-Oct
Feb	NONE

I would like the results to be displayed as follows:

Feb Mar Jul Oct
2   1	1   1

I have tried pivot tables but cannot force them to only work on the NONE data (so Feb returns a count of 3). I have tried various flavours of array formulae and suspect that this will yield the result I need eventually
I have not gone down the VBA route as I feel that Excel should be able to do this type extract/report.
Any help with this would be appreciated.

 

[SkipVought]

Hi,

Check out the COUNTIF function.

Also the PivotTable wizard can give you that answer without any formulas.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nasar]

Thanks for your prompt reply Skip.

I will give both your suggestions a try but I cannot at this stage see how I will only display the data for months Feb, Mar, Jul and Oct - I don't want to have months that do not have any NONE entries.

Will post back with my findings.

 

[SkipVought]

Add a column, CountIt, to your source data
[tt]
=IF(B2="NONE",1,0)
[/tt]
Do a PivotTable SUMMING CountIt
[tt]
Sum of CountIt
Month Total
Jan 0
Feb 2
Mar 1
Apr 0
May 0
Jun 0
Jul 1
Aug 0
Sep 0
Oct 1
Nov 0
Dec 0
[/tt]
Add a column to the right of the PT replicating the SUM

AutoFilter on that column - criteria > 0

Result
[tt]
Sum of CountIt
Month Total Filter
Feb 2 2
Mar 1 1
Jul 1 1
Oct 1 1
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

No need to even do that to be honest - pivot table your original data. ROW heading is MONTH, COUNT of RESPONSE as data item
Put RESPONSE as the PAGE field as well then pick "NONE" from page field et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[nasar]

Thanks Skip, Geoff,
Works a treat

Regards
Nasar