Macro/VBA based on Highlighted Cells?

[TSACov]

Hi everyone! (I originally posted this in the formula forum but was informed that this was a programming/VBA issue). Im not even sure what to Title this post...

Please see attached the document I will be referencing (ignore the #DIV/0! errors).

As you can see in Cells H4, I4 and J4, the formula's calculation draws from all data in the left hand side table and it works how it should do.

Now, in cells K4, L4 and M4, there should be a reflection of the formulas in H4, I4 and J4 respectively but with one difference. To exclude the cells highlighted in yellow.
The only stipulation you should know is that all formulas in these cells are dependent on being inside a date range (see table starting in cell H18). These 'date ranges' are split up to be P1, P2, P3 etc.

For example, K4 is based on H4. In this example, H4 includes 'Total Cost' within the date range P1 (1/1/2020 and 2/4/2020) from the left hand side table,
and K4 should do the same, minus highlighted cells which ALSO fit inside this date range (P1).

A simple way would be to just have the formula minus certain selected cells, but the highlighting could change, so I would want the formula to follow the highlighting rather than having to manually change it everytime
a piece of data is changed/added/removed.

The other thing is that I am also trying to do the same with the table starting H41, which is a replica of the table above it. It totals how many times a 'Source' occurs within a date range. I need it to calculate the same thing but minus
all anomalies of that 'Source' type, within its respective date range.

Everything that I am trying to change is highlighted in yellow.
This could be a series of silly questions but I am unsure as I am pretty new to this level of Excel, Thanks!

[maras_mak]

Does this do what you want?
With additional column E.
Enter 1 here if you want to exclude from the calculation.
Yellow - conditional formatting.
Look into the attachment.

[TSACov]

Hi Maras!

That is fantastic! I understand what you have done here in regards to SUM formula and I can definitely replicate that from now on.
My only question now is how to apply that to averages? E.G Cells L4 and M4, in respect to the original averaging formula in I4 amd J4.

[maras_mak]

I think it will work well now.