Exclude coloured cells from SumIfs

0
votes

I have a table like this:

Table for SumIfs

I need to create some Sumifs command in VBA to exclude green cells from criteria. I have the excel formula =SUMIFS(D:D;C:C;1) and need to add somehow next condition. In VBA I have:

Sub TotalItems()
    Dim sum As Integer
    sum = Application.WorksheetFunction.SumIfs(Range("D:D"), Range("C:C"), _
         3)
   MsgBox sum
End Sub

which returns 10.

I need to count only rows without any border colour in range "A:A". I tried to write the formula like this, but it returns an error:

 Application.WorksheetFunction.SumIfs(Range("D:D"), Range("C:C"), 3, Range("A:A").Interior.Color, 0)

Any idea how to solve this?

1
vbaexcelsumifs
What sets the color? Is it a conditional format? - Scott Craner
Yes, green colour is a conditional format. - otis
Okay what is the condition that makes it green then? You will need to use that instead. - Scott Craner
If it is conditional formatting, then the obvious and most robust solution is to target the condition that causes green rather than the color green per se. - John Coleman
in column A should be unique numbers. If some of them exists in another sheet then this cell is green coloured and this row should be excluded from sumifs - otis

1 Answers

2
votes

You can use SUMPRODUCT() no need for VBA:

=SUMPRODUCT((C1:C100=3)*(D1:D100)*(A1:A100 <>"")*(countif(Sheet2!A:A,A1:A100)=1))