Count of 'Yes' values

[Duncs]

Hi all. Look slike it's a busy day for me!

What I want to do is display a count of all 'Yes' values that have
been entered against a specific date.

For example if columns A & B hold the following:

A B
01/11/2008 Yes
01/11/2008 No
01/11/2008
01/11/2008 Yes
02/11/2008
02/11/2008
03/11/2008 No
03/11/2008 No
03/11/2008 No
03/11/2008 Yes
04/11/2008 Yes

I'd like to display a summary that shows:

01/11/2008 2
02/11/2008 0
03/11/2008 1
04/11/2008 1

Is this possible?

TIA

Duncs

 

[Harald Staff]

Hi Duncs

A Pivot table does this and more in no time.

HTH. Best wishes Harald

 

[Duncs]

The problme with a Pivot table Harald is, the spreadsheet is shared
and I need to 'un-share' it before I can update the pivot table. I
also loose all functionaility of the pivot table, such as filtering
entries in it when it is shared.

Rgds

Duncs

 

[Pete_UK]

Okay, suppose you wanted your dates in column D and the count in
column E - put this in D1:

=MIN(A:A)

to find the earliest date and format the cell as a date. Then put this
in D2:

=D1+1

format as date and copy down as far as required.

Then put this in E1:

=SUMPRODUCT((A$1:A$100=D1)*(B$1:B$100="yes"))

and copy down.

Hope this helps.

Pete

 

[Harald Staff]

I see. Would it be possible to link, or query, an external unshared
workbook to the data and create the table there ?
I have a feeling that the amount of data and complexity of your goal exceeds
your example, so a formula solution may not be what you need. May be wrong
of course

Best wishes Harald


The problme with a Pivot table Harald is, the spreadsheet is shared
and I need to 'un-share' it before I can update the pivot table. I
also loose all functionaility of the pivot table, such as filtering
entries in it when it is shared.

Rgds

Duncs

 

[Duncs]

Harald / Pete_UK,

Many thanks to you both for your responses.

I've gone for a solution that is based on Pete's suggestion, which
makes things nice and simple and keeps it 'clean'.

Thanks to both of you for your help.

Rgds

Duncs

 

[Pete_UK]

You're welcome, Duncs - thanks for feeding back.

Pete