# SUMIF array question

##### New Member
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?

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi,

Try:

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

Enterd with Ctrl + shift + enter

Also:

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

works.

Tazguy37 said:
Also:

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

works.

Are you sure?

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!

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?

Tazguy37 said:
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
FGHIJKLM
1
2OfficeOfficeOffice
3
4#N/A
54
6
74#N/A
8
9
10
Sheet1

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.

fairwinds said:
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.

Replies
5
Views
370
Replies
1
Views
310
Replies
2
Views
159
Replies
11
Views
903
Replies
14
Views
867

1,203,169
Messages
6,053,865
Members
444,690
Latest member
itgldmrt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back