Possible to have ColorFunction & Sumproduct together?

[unley]

Or shall I call it Count by Color that match Criteria

I've attached a dummy workbook where you can see tasks completed and not completed in each month in each column ( only July & August in this dummy workbook ) and used ColorFunction function.

Now the managers want to see how many type of tasks ( footpath repair, kerb repair ) are completed and not completed in that particular month.

Is it possible to use sumproduct (or any other way) with ColorFuntion? So we can match or sum the ColorFuntion with type of tasks in J Column?

My questin in the dummy workbook is in the blue quote


Task dummy2.xls


Thank you in advance

[pike]

Hi unley
you could return an array from a UDF to use with the sumproduct

Please Login or Register  to view this content.

[unley]

Thank you pike for replying. I used your code but could not add up.

I've enclosed the updated workbook with you code.


Task dummy3.xls


Can you attach the workbook you updated so I can see how it works? Thank you

I've tried all the search beforehand but could not find any that suit my need

[pike]

hi unley,
Why dont you use a pivot table?.

You are heading in the wrong direction with Colour these function are Volatile.
The array is only horizontal

In you use a 1 or 0 formated the same colour as the cell you will be better off as you can sumproduct to do what ever you need

[unley]

Thank you pike. But I'm a bit lost due to so much overtimes lol

I'm not so sure what you mean by use 1 or 0 formatted the same color but I do want array in horizontal (it's the only way of counting nature of tasks in J column really)

The reason I don't use pivot table is because the other people with little Excel experience wanted to see the result and also I create graphs using those tables of Task result ( in light yellow tables) and report them to elected members.

Also there are six different wards in the Local Government Arial in the original workbook.