Arrays in Excel

[Griffey5]

Using the =SUM(IF) array Excel can add an array with 2 and conditions such as
(=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")'Batch Log'!F2:F2001,0)) but it cannot add an array
with 3 conditions such as (=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))
Why?

 

[ShaneDevenshire]

Hi.

First Excel can run 20 conditions if you want, or more. Second I would
consider SUMPRODUCT since it does not require array entry.

Now let's look at your formula:

(=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch

Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

1. You don't need the out ().
2. You don't need the IF
3. Replace this with an array entered SUM:

=SUM(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02")*'Batch
Log'!F2:F2001)

or use SUMPRODUCT without array entry:

=SUMPRODUCT(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02")*'Batch
Log'!F2:F2001)

The fact is there are a number of other changes we might make but for a
starter see how this works.