SUMPRODUCT unless row a certain colour

[dshilan]

Hi,

Is it possible to use the below formula, but for it to not include rows that a certain colour?


=SUMPRODUCT(--($H$2:$H$65536=2),--($Y$2:$Y$65536=7),$AA$2:$AA$65536)/100

[judgeh59]

Is the colour being set manually or by conditional formatting?

[dshilan]

Hi Judge it will be manually,

Background: it is a file of test transactions, if there is a voided line in a transaction it will appear in say Y1 and Y2 with the same value, X1 will be a plus and X2 a minus. So would like to ignore these. But as they are two in rows SUMProduct cant account for this.

[judgeh59]

okay....that makes it a bit more difficult....if it was done using CF you could have incorporated the same logic in an IF statement that was used in the CF logic....somebody in this forum wrote a UDF that told you if a cell was coloured....so if you can find that, you can use it in an IF statement....IE: IF(Coloured, Do SUMPRODUCT, DoNothing)....let me know if you can't find it....maybe I can...

[dshilan]

I found this and added it in, can now see as a UDF

Http://www.ozgrid.com/VBA/ReturnCellColor.htm

not quite sure how to amend formula though.

[dshilan]

Think this may be better

<Code>
Function Filled(MyCell As Range)
If MyCell.Interior.ColorIndex > 0 Then
Result = 1
Else
Result = ""
End If
Filled = Result
<\Code>

End Function

[judgeh59]

by the way your [\code] should be [/code].....I did that the other day too....

[judgeh59]

looks a lot easier....do you need help on the formula to incorporate that with your SUMPRODUCT?

[dshilan]

Yeah, soon as I hit enter I realised that, spend most day looking at xml too :S

Yes I wouldnt mind....

[judgeh59]

So I would change the function to return a 0 (that's a zero) instead of a ""....then try something like this...

=IF(Filled(A1),SUMPRODUCT(),"")......in english....if the cell colour in A1 is anything but white do SUMPRODUCT else fill in the field with a blank (or anything else you want to the cell to display)...

[dshilan]

Sorry to keep on, but when I do this

=IF(Filled(AA7),"yes","no") i get a yes if filled but if not i get #value! - any ideas?

[judgeh59]

wait....do you set the function return value for "" to a zero?

[judgeh59]

IE:

Function Filled(MyCell As Range)
If MyCell.Interior.ColorIndex > 0 Then
Result = 1
Else
Result = 0
End If
Filled = Result

[judgeh59]

not without see the file...can you upload the file?

[dshilan]

Sorry, I have now, so that is all working great

Just need to figure out how to combine with the SUMPRODUCT, missing out the rows that are coloured

[judgeh59]

sweeeeeeeeeeet.....glad it worked out....

[judgeh59]

remember to set the thread/post to solved if this fixed your issue....