countif combined with logical and

[lpvdsteen]

Hi,

My problem is as follows:

I have a row in which a number of cells are non blank

Book3.xls
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY
9	00:00				01:00				02:00				03:00				04:00				05:00				06:00				07:00				08:00				09:00				10:00				11:00				12:00				13:00				14:00				15:00				16:00				17:00				18:00				19:00				20:00				21:00				22:00				23:00
10																																																											I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I	I
Bond, J

a similar row, on another worksheet in the same workbook, contains a specific value for each colored cell, eg. all blue cells contain the number 1, all yellow cells contain the number 2, all green cells the number 3, and as such another 4 colors.

I need a formula, non VBA, to count cells that are a; not empty in the displayed selection AND contain a number (or are in a specific colored selection) on the other sheet.

In the example, one formula checking for filled blue cells should give me 2, and filled yellow cells should give me 8.

the VBA formula is simple and checks for color;

where ortbereik is the range to count and check, and ortcolor is the color to compare with<font face=Courier New><SPAN style="color:#00007F">Public</SPAN><SPAN style="color:#00007F">Function</SPAN> ortcalc(ortbereik, ortcolor)<SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> ortcel<SPAN style="color:#00007F">In</SPAN> ortbereik<SPAN style="color:#00007F">If</SPAN> ortcel.Interior.ColorIndex = ortcolor.Interior.ColorIndex And ortcel<> ""<SPAN style="color:#00007F">Then</SPAN> ortcalc = ortcalc + 0.25<SPAN style="color:#00007F">Next</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

but I can't copy a VBA formula programmatically into a new workbook without having to perform programmatic changes which I can't ask others to do. Hence my question for a non VBA formula

If anyone can help, be much obliged

Luke

 

[just_jon]

Assumptions:

1] Range1 is to be checked for non-blank cells and is on Sheet1, H1:X1
2] Range2 is to be checked for a number, which will be given in Sheet1 cell A1, and is on Sheet2!H1:X1
3] in Sheet1, B1, return the count of non-blank cells in Range1 where the same cell in Range2 = Sheet1!A1

=SUMPRODUCT((H1:X1<>"")*(Sheet2!H1:X1=A1))

This should work as long as this statement - "...a similar row, on another worksheet in the same workbook, contains a specific value for each colored cell, eg. all blue cells contain the number 1, all yellow cells contain the number 2, all green cells the number 3, and as such another 4 colors." Your posted exhibit, however, shows all 1's.