Count cells with criteria in merged cells

[schorka]

Hi there,

I do have the following problem, where I didn't find a solution yet:

I have a long spreadsheet full of data, unfortunately with merged cells. The merged cells have a priority assigned, and I want to count how many cells in another column have this special priority.

In this example, I want to know how many components have priority X, and how many of priority X are green/red (Done/Undone).

To count the coloured ones I found a working VB module for. But not if a special condition (in this case priority) has to be match as well.
So I thought I just continue with the amount of components, because they're already filtered then by priority.

I cannot unmerge all the cells, because there will be several update spreadsheets sent to me, so I'd have to do it again and again...

Hope the problem is clear, any help is much appreciated!

Cheers,
George

[Glenn Kennedy]

I'd be tempted to ask those who send you the sheets to stop merging the cells! Simplest way, in D2, copied down:

=IF(A2="",D1,A2)

and then in G9, copied across:

=COUNTIF($D:$D,G8)

[schorka]

Thank you so much Glenn!

Yes, I thought about that, but a colleague said that won't happen...

One further question: The priorities are separated with an empty row between them, is there a way that Excel just ignores these rows (so goes directly on with the next priority? With the solution above, it just puts the last value also in this row.

Sorry, I forgot to set it up like that...

And also how can I count the cells with the (green) background and the corresponding priority?

Many thanks!

[Glenn Kennedy]

Change the helper, in D2 to:

=IF(B2="","",IF(A2="",D1,A2))

I thought yu said you had a VBA solution to count the cell colours? Since the cells are filled manually with their clour, you will need VBA, in some shape or form, as it is not a "value" present as a result of a calculation.

[Glenn Kennedy]

Here's one way:

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


Then:
=SUMPRODUCT(($D$2:$D$30=G9)*testColour($C$2:$C$30,$F$11))

But.... you have to refresh calculations (F9 on my laptop) if shadings are changed.

[Bosco]

Or...........

Try this formula way without helper column and VBA.

1] In G10, formula copied across to K10 :

=SUMPRODUCT(($B$2:$B$30<>"")*(LOOKUP(ROW($A$2:$A$30),ROW($A$2:$A$30)/($A$2:$A$30<>""),$A$2:$A$30)=G$9))

2] Define Name >>

>> Name : CountDone

>> Refers to :

=SUMPRODUCT(($B$2:$B$30<>"")*(LOOKUP(ROW($A$2:$A$30),ROW($A$2:$A$30)/($A$2:$A$30<>""),$A$2:$A$30)=E$9)*(GET.CELL(63,IF(1,+OFFSET($C$2:$C$30,ROW($C$2:$C$30)-MIN(ROW($C$2:$C$30)),)))=GET.CELL(63,$F$11)))


Then,

In G11, formula copied across to K11 :

p.s. Get.Cell() is a Excel-4 Macro function, so you need to save file as xlsm.

Regards
Bosco

[schorka]

Hi Bosco,

thanks very much for the explanation!
I did it as you mentioned in my real spreadsheet, but Excel don't accept the formula, esp. the get.cell() term. The Workbook is already saved as a .xlsm.

And which cell has to be defined as CountDone (can I also define it directly as CountXYZ bc I have several columns to be counted separately)?

(...) I thought yu said you had a VBA solution to count the cell colours? Since the cells are filled manually with their clour, you will need VBA, in some shape or form, as it is not a "value" present as a result of a calculation.

Yeah, it worked actually but somehow it now just gives out #NAME...

[Glenn Kennedy]

Did you enable macros on opening?

[schorka]

I saved it as a .xlsm, so I thought they're enabled automatically then?

[FlameRetired]

No. It's an download safety feature. Upon download you first click the Enable editing button then click Enable content. That last permits the Named formula CountDone to operate. The #NAME error will vanish and the formula will calculate.

[schorka]

Thanks, the problem is though, only the enable editing button appears. I don't know how to enable the content afterwards. The tutorial found online via Trust Center doen't work, all options are disabled.

[FlameRetired]

Then I would check the Options > Trust Center settings > Message bar settings. The only way I can duplicate what you describe is by choosing the "Never show information about blocked content". Choose the other one instead "Show the message bar in all applications when active content, such as ActiveX controls and macros, has been blocked."

Did this help?

[schorka]

No, "show the message bar" is selected.

I'm not sure if I did it the right way with defining the name etc. as explained in the file above though, and also I did not add anything as a module in VBA.
Macros in general should work, sometimes the bar about enabling content appears and I click on enable.

[Glenn Kennedy]

Which solution did you try to implement?

[schorka]

I tried both, yours and Boscos. The problem must be in the disabled marcros somehow. Now I try to find it out with someone else.

[schorka]

So, the problem was the location of the file, some are not allowed for using macros.
But I do have one further question about the way Bosco provided:

What is the role of cell E9 (part of the formula)? Should I just use the one left to priority 1 in my spreadsheet? And GET.CELL(63,IF, , what does 63 stands for, do I have to change something there as well? Because Excel still responds with an error message "Function isn't valid" and highlights the GET.CELL term, but the colorfunction term one post above is working now somehow.