Counting Non-Contiguous Cells

[Sam Hamman]

Hi, this is probably very basic and I am being very stupid but if I
wanted to count the values of Cells D1 F1 and G1 I which have the
background colour of yellow I would use the following

CoiuntYellow(D1)+CountYellow(F1)+CountYellow (G1)

Is there an yeasier way and if so what is the correct way to enter the
formula.

I ahve tried CountYellow(D1,F1,G1) and it throws an error

Any help appreciated

Thanks

Sam

 

[isabelle]

hi Sam,

what does "CountYellow" is it a new function of xl2012 ?


--
isabelle



Le 2012-02-16 18:11, Sam Hamman a écrit :

 

[Sam Hamman]

Hi Isabelle, no it is a custom function I only used it as an
example.....


All I want to do is know the format for counting non continguous
cells.

For contiguous cells it would be Count(A2:A45) but I dont know hwo to
do Count(A2, A5, A7)


Thanks

Sam

 

[Rutger]

Hi Isabelle, no it is a custom function I only used it as an
example.....


All I want to do is know the format for counting non continguous
cells.

try sum()

 

[Sam Hamman]

Thanks Rutger but what would be thr format ?

sum(??????)

thanks

Sam

 

[isabelle]

ok, try with ParamArray

Function CountYellow(ParamArray rg()) As Long

 

[isabelle]

note that if there are a cell color change there are no trigger event and the formula Will not be recalculate

=SumColor(6,A1:A2,A5:A6,A8:A9)


Function SumColor(iColor As Integer, ParamArray rng() As Variant) As Long
Application.Volatile
For i = LBound(rng) To UBound(rng)
For Each c In rng(i)
If Range(c.Address).Interior.ColorIndex = iColor Then
SumColor = SumColor + Range(c.Address)
End If
Next
Next
End Function

 

[isabelle]

correction:

Function SumColor(iColor As Integer, ParamArray rng() As Variant) As Long
Dim i As Long, c As Range
Application.Volatile
For i = LBound(rng) To UBound(rng)
For Each c In rng(i)
If Range(c.Address).Interior.ColorIndex = iColor Then
SumColor = SumColor + Range(c.Address)
End If
Next
Next
End Function

 

[Zaidy036]

Hi, this is probably very basic and I am being very stupid but if I
wanted to count the values of Cells D1 F1 and G1 I which have the
background colour of yellow I would use the following

CoiuntYellow(D1)+CountYellow(F1)+CountYellow (G1)

Is there an yeasier way and if so what is the correct way to enter the
formula.

I ahve tried CountYellow(D1,F1,G1) and it throws an error

Any help appreciated

Thanks

Sam

Free ASAP Utilities has a ASAPSUMBYCELLCOLOR function

 

[Sam Hamman]

Thanks Zaidy but i think i have thrown a spanner in the works by using
the CountYellow as an example


Simply put if I had the following

C1 value is 23
D1 value is 45
G1 value is 25

And i wanted to sum the numbers would the formula be

Sum(C1)+Sum(D1)+Sum(G1) or can it be simpler ?

I thought Sum(C1,D1,G1) would do but it doesnt work

Thanks

Sam

 

[Ron Rosenfeld]

Thanks Zaidy but i think i have thrown a spanner in the works by using
the CountYellow as an example


Simply put if I had the following

C1 value is 23
D1 value is 45
G1 value is 25

And i wanted to sum the numbers would the formula be

Sum(C1)+Sum(D1)+Sum(G1) or can it be simpler ?

I thought Sum(C1,D1,G1) would do but it doesnt work

There is no question but that

=Sum(C1,D1,G1)

will add the contents of C1, D1 and G1. Depending on your version of Excel, you may have a 31 or 255 argument limit to a function.

But Sum(C1,D1,G1) is very different from your original question.

If you want to do something similar with a UDF, the UDF must be written so as to allow that kind of argument. That is a function of the UDF. If the UDF is not written to handle multiple arrays, then you can only add things individually:

e.g: CountYellow(C1) + CountYellow(D1) + CountYellow(G1) or, if you can live with the argument number limitation, you could use:

=SUM(CountYellow(C1), CountYellow(D1), CountYellow(G1) )

One method of handling multiple arguments of this type, where the number of arguments is unknown, is by using the ParamArray argument in your UDF Function statement; and adding the appropriate code to handle that. Isabelle has already written about that in this thread.

 

[Claus Busch]

Hi Sam,

Am Fri, 17 Feb 2012 19:05:10 +0000 schrieb Sam Hamman:

C1 value is 23
D1 value is 45
G1 value is 25

SUM ignores text. So if E1 and F1 are blank or text, you can use:
=SUM(C1:G1)


Regards
Claus Busch

 

[Sam Hamman]

Hi Ron, hope you are well and thanks to all who have contributed to
this thread.

Not for the first time have I not made myself abundantly clear and I
am slowly learning the lesson lol..

Ron, I have not been able to progress my spreadsheet that you
previously helped me with (you may recall from the countYellow
function !)

However, I do have two what appear to me to be very complex queries
which I hope you (or others) can help me with with.

Once I have it clear in my head I will post again.

Thanks in advance

Sam

 

[isabelle]

it is also possible to add a condition to verify that the cell contains a numeric value

Function SumColor(iColor As Integer, ParamArray rng() As Variant) As Long
Dim i As Long, c As Range
Application.Volatile
For i = LBound(rng) To UBound(rng)
For Each c In rng(i)
If Range(c.Address).Interior.ColorIndex = iColor Then
If IsNumeric(Range(c.Address)) Then
SumColor = SumColor + Range(c.Address)
End If
End If
Next
Next
End Function