Trying to count cells that meet more than one Criteria

[suziq729]

Help! I have been trying to get a total of cells where they contain "Y" AND have a certain fill color. Nothing I've tried is working. Here are the formulas that have not worked so far. Any ideas??

=COUNT((E9:E491="Y")*(countcolor(B4,E9:E491))*E9:E491)

=COUNT((E9:E491="Y"),((countcolor(B5,E9:E491))))

=COUNT((E9:E491="Y")+((countcolor(B4,E9:E491))))

Thanks...Sue

 

[suziq729]

Not to this extent I don't think....what is your specific question?

 

[suziq729]

Also, I was not using count at all....please look at the formula above including SUMPRODUCT - that is the closest I have gotten thus far, but it returns "0" every time...

 

[Aladin Akyurek]

Ok...I think I am getting closer, but it is now returning "0" every time. Here is what I entered after copying the code into Editor:

=SUMPRODUCT(--(ExtCell("backgroundcolor",D9:D482,1)=B5),--(D9:D482="Y"))

Where B5 is filled with the desired color. I think that might be where I am going wrong?

What do you have in B5 - an integer?

 

[suziq729]

No...I only have the cell filled with a color....I thought the countcolor formual I had would recognize it. That formula is:

Function CountColor(ColorRange As Range, Target As Range) As Long
Dim c As Range
For Each c In Target
If c.Interior.ColorIndex = ColorRange.Interior.ColorIndex Then
CountColor = CountColor + 1
End If
Next
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

 

[Aladin Akyurek]

No...I only have the cell filled with a color....I thought the countcolor formual I had would recognize it. That formula is:

Function CountColor(ColorRange As Range, Target As Range) As Long
Dim c As Range
For Each c In Target
If c.Interior.ColorIndex = ColorRange.Interior.ColorIndex Then
CountColor = CountColor + 1
End If
Next
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

So, the exhibit I posted is not what you're looking for?

 

[poorwallace]

Aladin,

I'm intrigued by the code you posted from Harlan Grove. However, I've been unable to get it to work for me in this context

=IF(ExtCell("backgroundcolor",A3:A9)=B4,"yes","no")

The formula returns no, which is false, because the colors are indeed the same. I've also tried it with just A3 being equal to B4. I would like to know how to utilize this function (which, BTW, I think is great--thanks for sharing!).

Any further comments on this function would be greatly appreciated.

Thanks
wally

 

[Aladin Akyurek]

Aladin,

I'm intrigued by the code you posted from Harlan Grove. However, I've been unable to get it to work for me in this context

=IF(ExtCell("backgroundcolor",A3:A9)=B4,"yes","no")

The formula returns no, which is false, because the colors are indeed the same. I've also tried it with just A3 being equal to B4. I would like to know how to utilize this function (which, BTW, I think is great--thanks for sharing!).

Any further comments on this function would be greatly appreciated.

Thanks
wally

You need to read the comments accompanying the code... Since you feed a range to ExtCell, the function must be informed that you want to return an array like in:

=IF(OR(ExtCell("backgroundcolor",A3:A9,1)=B4),"yes","no")

or simply...

=IF(ISNUMBER(MATCH(B4,ExtCell("backgroundcolor",A3:A9,1),0)),"yes","no")

 

[Krishnakumar]

Hi,

Another option,

Example2.xls
	B	C	D	E	F	G
2	y	6		3
3	n	3
4	y	3	Color	=GET.CELL(38,Sheet2!$B4)
5	y	6
6	n	3
7	y	3
8	y	6
9	y	3
10	n	3
Sheet2

Defined Color as

=GET.CELL(38,Sheet2!$B2)

Formula in E2,

=SUMPRODUCT(--(C2:C10=6),--(B2:B10="y"))

HTH

 

[isadoko]

Krishakumar,

Thanks for your solution.