Excel Array Formula Error

Question

I am coming to seek guidance with another question with Excel Array Formulas.

I am using Excel 2003 and trying to understand a new spreadsheet with the following type of formula :

{=IF($B$6,SUM(($C$6:$AM$6=1)*1),)}

I have tried using the Excel formula audit tool to understand the formula but it crashes when I run it on these type of array formula.

As of now I am thinking that the formula does this :

B6 has a number = True / False
If True do SUM of (C6:AM6=1) and multiply by 1
If False do nothing

However, I am not 100% of the second statement. Does it say SUM the number of times 1 is present in C6:M6 then multiply by 1? If so why multiply by 1. My only guess on the latter is that the (C6:M6=1) returns a True or False value and the *1 converts it to 0 or 1. Then if this is correct, what is the purpose of the sum function?

Thanks for any guidance.

Rory · Accepted Answer · 2014-05-21 15:50:52Z

5

You are correct.

($C$6:$AM$6=1) returns an array of TRUE/FALSE values, which SUM would ignore.

Multiplying by 1 creates an array of 1/0 values, which are then added by SUM to create a count of the number of cells in the range that equal 1.

COUNTIF would be simpler as mentioned previously.

answered May 21, 2014 at 15:50

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Iconz255 Over a year ago

Thanks for your help @Rory. As mentionned I could use non array functions but that would mean starting with a fresh file. So I am using the current one and learning new things along the way.

Gary's Student · Accepted Answer · 2014-05-21 15:49:08Z

1

I think you do not need an array function. Try this non-array formula:

=IF($B$6,SUMIF($C$6:$AM$6,1,$C$6:$AM$6),"Do Nothing")

answered May 21, 2014 at 15:49

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

Iconz255 Over a year ago

Thanks for the help @Garys Student. The file I currently have contains all these arrays. I could either use them or start a new file from scratch. So better learn new things along the way.

Collectives™ on Stack Overflow

Excel Array Formula Error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related