Sum the amount of the cells with a background RED

[drom]

Hi and thanks in advance!

I have the following name created in my workbook

BackGround=GET.CELL(63;INDIRECT("rc";FALSE))
so if I put in

A2 wich has a BackGround Colour equal to green the formula = BackGround I will get 43
A3 wich has a BackGround Colour equal to RED the formula = BackGround I will get 3
A4 wich has a BackGround Colour equal to Black the formula = BackGround I will get 1
​

This is working ok but I would like to sum for a given range the total if their BackGround Colour is equal to RED so equal to 3 according to BackGround formula

but when I use:
=SUMIF($Z$1:$Z$300;BackGround=3;$Z$1:$Z$300) I get 0
because the second parameter is not giving me a array

I was trying to use:
=SUMPRODUCT(--(BackGround(Z1:Z300)=3);Z1:Z300)
but does not work

Any idea on how to sum in for a given range the total sum for the cells with a Red background

I know how to do this using VBA.

I would like to know how can I do this using a formula

 

[shg]

I know how to do this using VBA. I would like to know how can I do this using a formula.

As a curiosity, why? If you use GET.CELL, you still have to save the resulting workbook as macro-enabled.

 

[drom]

I do not know if using this say new formula the file is going to update the values fast.
using UDF's if I change from RED to xlnone, the UDF formula does not update the result
anyway my workbook has many other macros and for this I would like to know if is possible without macros and/or UDF's
I am using Get.Formula because I have seen in a web, but if you know any other way...

 

[shg]

If you found a way for it to work, it would still not update automatically when cell color is changed, because doing so doesn't trigger calculation. Excel doesn't consider color as data.

 

[drom]

Yes but F9 does not update the UDF formula based on the backGround color
but the formula BackGround=GET.CELL(63;INDIRECT("rc";FALSE))
is Updated everytime I press F9
This is why I would like to know if I can use a combination of Sumproduct with Get.Cell for a given range or Sumif + Get.Cell and/or...

 

[shg]

Yes but F9 does not update the UDF formula based on the backGround color

It will if you put Application.Volatile in the UDF.