Excel 2007 Sub total cells and counting

[mshaji]

Hi

I have excel data sheet with lots of cells and I used subtotal function to categorize the data as below.

A1 No AA
A1 Yes BB
A1 2
A2 No CC
A2 1
A3 No DD
A3 Yes DE
A3 Yes BD
A3 Yes ND
A3 4

I need to count the no. of sub-group which will have atleast one value as "Yes".In the above case it would be 2 i.e.A1&A3

Any help on above ? Many thanks in advance !!!

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[mshaji]

Thanks for the suggestion ! I have included a sample excel sheet with the post and I hope it will help in understanding my query.

[DonkeyOte]

One possibility

Please Login or Register  to view this content.

If preferred you could use H10 in H9 and subtract H8 from result - then have H10 = sum of H8:H9

Note the Array entry requirement for H8 - you could use a SUMPRODUCT equivalent:

Please Login or Register  to view this content.

but the Array will be slightly more efficient.

[mshaji]

Thank you for the reply ... It works both ways