SUMIF array question

[Mr Canoehead]

I have the following formula to sum the contents of Cells I7:EF7 if the cell contains a number (some cells will have errors) only if the corresponding cell in row 2 contains the text "office"

{=SUM(IF(AND(I$2:EF$2="Office",(ISNUMBER(I7:EF7))),I7:EF7,0)}

with the current formula I don't get an error in the cell but the sum is always 0, even when the conditions are met.

Any suggestions?

 

[fairwinds]

Hi,

Try:

=SUM(IF((I$2:EF$2="Office")*ISNUMBER(I7:EF7),I7:EF7))

Enterd with Ctrl + shift + enter

 

[Tazguy37]

Also:

=SUMPRODUCT(--(I2:EF2="office"),--(ISNUMBER(I7:EF7)),I7:EF7)

works.

 

[fairwinds]

Also:

=SUMPRODUCT(--(I2:EF2="office"),--(ISNUMBER(I7:EF7)),I7:EF7)

works.

Are you sure?

 

[Tazguy37]

Worked for me. If you have a sheet sample where it doesn't work, I'd like to see it (I wouldn't want to give out false info). Thanks!

 

[Mr Canoehead]

Thanks Guys,


Looks like they do both work. Looking at both formulas, when Excel multiplies 2 logical values ie. True*True is the result True or 1?

 

[fairwinds]

Worked for me. If you have a sheet sample where it doesn't work, I'd like to see it (I wouldn't want to give out false info). Thanks!

Sure. And I'm ofcourse referring to that OP stated that the cells can hold errors.

Book1
	F	G	H	I	J	K	L	M
1
2				Office	Office	Office
3
4		#N/A
5		4
6
7				4		#N/A
8
9
10
Sheet1

 

[fairwinds]

Thanks Guys,


Looks like they do both work. Looking at both formulas, when Excel multiplies 2 logical values ie. True*True is the result True or 1?

As soon as TRUE is subjected to a mathemativcal operation it coerces into 1 (and FALSE to 0) thus TRUE*TRUE equals 1

An IF statment recognises 0 as FALSE and any other numerical value as TRUE.

 

[Tazguy37]

Sure. And I'm ofcourse referring to that OP stated that the cells can hold errors.

You're quite right. I read that the first time through, but I think my brain dropped it somewhere along the way.