Countifs Formula returns #Value error

[npett]

[FONT=&quot]Hi I am trying to create a formula that will count cells that are true, and are a certain colour. I have a macro in place that allows me to count by colout.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]My formula right now is =COUNTIFS(IF(W2:W200,"YES"),colorfunction(AN2,A2:A200, FALSE))
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]If anyone has any insight on how to make this work it would be immensely helpful!
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Thanks,
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Nick[/FONT]

 

[Foz1980]

For colorfunction to work, you need VBA coding.
Have you already got the coding but it's just the formula that won't work?

 

[Rick Rothstein]

I have a macro in place that allows me to count by colout.

Post the code for it.

 

[Joe4]

Your function is very confusing to me:

[COLOR=#000000]=COUNTIFS(IF(W2:W200,"YES"),colorfunction(AN2,A2:A200, FALSE))[/COLOR]

The first argument of the COUNTIFS function is a range (https://www.techonthenet.com/excel/formulas/countifs.php).
I don't think IF(W2:W200,"YES") returns a valid range.

Also, maybe I am wrong, I do not believe that you can use User Defined Functions inside of your COUNTIFS function.
At least my tests don't seem to work.

 

[npett]

Hi Rick,

Here is the UDF code

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

 

[npett]

I think you are right Joe it was just the only formula I could find that didn't return a syntax error.

I'm using this to track quotes in certain postal codes. The colour relates to a cheap quote and the range refers to the postal codes. I am trying to find a way to calculate the amount of quotes that are cheap and in certain postal codes.

Do you know of a different formula that might work?

Thanks,

Nick

 

[Joe4]

I think you may need a table that indicates those things instead of trying to do it by color.

If you can post a small example of your data and your expected results, we can probably help you come up with a formula, if you are table to create such a lookup table.

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.