Sumproduct

[crapshoot]

I am running Excel 2002 and have a counting question. My fields are row 212 columns H to BN and row 209 columns H to BN, I want to count If any cell in row 212 = 0 on condition that the matching column in row 209 is >= to 1 ... what would my formula be?

 

[WhatTheXL]

Try:

=SUMPRODUCT(--(H212:BN212=0),--(H209:BN209>=1))

 

[Peter_SSs]

Possibly
=SUMPRODUCT(--(H212:BN212=0),--(H209:BN209>=1))
but note that this would count any blank cells in row 212 as 0.

If you might have blanks in row 212 and don't want to include them in your count, try
=SUMPRODUCT(--(H212:BN212=0),--(H209:BN209>=1),--(H212:BN212<>""))

 

[WhatTheXL]

Good Catch Peter

Another way to is to check if it is numeric.

=SUMPRODUCT(--(ISNUMBER(H212:BN212)),--(H212:BN212=0),--(H209:BN209>=1))

That is why I come here. it helps me hone my skills.
Thank You

 

[crapshoot]

Thanks Peter, can I use formatting instead of value, say blue cell in row 212 to matching red cell in row 209 matching column?

 

[Peter_SSs]

Thanks Peter, can I use formatting instead of value, say blue cell in row 212 to matching red cell in row 209 matching column?

Basically the answer is 'No', using standard formulas and assuming the colours are applied to the cells manually.

I haven't thought it right through but possibly if the colours are the result of Conditional Formatting there may be a way.

How are the colours applied to your cells - manually of CF?
If CF, tell us what the CF rules for each row are.

 

[crapshoot]

It did not work. I ran =SUMPRODUCT(--(H212:BN212=1),--(H209:BN209=0)) where AH 212 was 1 and AH 209 was 0 also BA 212 was 1 and BA 209 was 0, my results should have been 2, I got 0. I asked for when row 212 was 0 and 209 was 1 or higher, I reversed them but also reversed the formula.


ran

 

[Peter_SSs]

It did not work. I ran =SUMPRODUCT(--(H212:BN212=1),--(H209:BN209=0)) where AH 212 was 1 and AH 209 was 0 also BA 212 was 1 and BA 209 was 0, my results should have been 2, I got 0. I asked for when row 212 was 0 and 209 was 1 or higher, I reversed them but also reversed the formula.


ran

You didn't quite reverse the formula in that you don't have a >=1 any more. Never-the-less you new formula works for me with that data:

Excel Workbook
	E	F	G	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
209	2																													0																			0
210
211
212																														1																			1
SUMPRODUCT

Perhaps one or both of your rows 209 and 212 are not actually numbers, but text.

Could that be the case?

If you put these formulas in spare cells, what do they return?
=ISNUMBER(AH209)
=ISNUMBER(AH212)

 

[crapshoot]

? I tried it in a blank workbook and still got 0

I got #'s for =ISNUMBER(AH209)
=ISNUMBER(AH212) and if I placed my mouse over the #, I got true

 

[Peter_SSs]

? I tried it in a blank workbook and still got 0

I got #'s for =ISNUMBER(AH209)
=ISNUMBER(AH212) and if I placed my mouse over the #, I got true

What about for ISNUMBER() with AH212, BA209 and BA212?

I've posted a screen shot of my actual sheet and formula, so you can see it is working for me.
We need to determine what is different about your sheet.

Did you set up the balnk workbook with just the values shown in my sheet?
Are the numbers in rows 209 and 212 just entered directly, or are they formulas?

Take some more spare cells and see what these give
=COUNT(H209:BN209)
=COUNT(H212:BN212)
=SUM(H209:BN209)
=SUM(H212:BN212)