Incorporate SUM by cell color in a formula with HSTACK, MAP and LAMBDA.

Hi,

I am purely guessing that the solution will need a VBA script, hence me posting here.

I have a formula that summarizes planed driven km in a set of specific countries.
I have a second formula that uses TEXTJOIN to list the calendar weeks each country is planned.

To get an idea (sample attached below):
SampleKMCW.PNG

In B3 I use this code:

Please Login or Register  to view this content.

In C3 I use this code:

Please Login or Register  to view this content.

As it it works now it summarizes everything and disregarding color. I need to incorporate the ability to only summarize the "Blue" ones (both KM and CW's) and optimally, if it is possible, to toggle between "Blue" and "Green" (from referenced color cell).

If we take DK for example, and the result I would like to get:
DK: 500 KM (CW14) -- Blue cells
DK: 200 KM (CW12; CW13) -- Green cells


Can this be done?
Or do you have any other solutions to this problem?

Welcome to the forum.

What makes the cells blue or green? Colour and other formatting cannot be used by a formula - sorry.

Is there a logical RULE that determines whether a cell is colured green/bue?

Well, it's going to have to be VBA, I am afraid.

Is this what you'd intended. It's hard to tell, as you edited the first thread after replies were recieved (better to make changes in the thread...).

Formula:

Please Login or Register  to view this content.

And for the code (which I just noticed also changes!!):

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.