Array Formula w/ 4 or 5 Criteria

[Stacy_Sutton]

Is there a limit to the number of criteria that can be used in an array formula? I think my formula is correct but it's not working. I suspect it might be a format issue. I'm sure this can be done but maybe I'm not using the best formula.

I have a list of data (CM's 2-1-11 To 1-31-12) where each row contains:

Customer ID (Col. C)
Period (Col. F)
G/L Account (Col. H)
Amount (Col. K)
Brand (Col. L)
Prior Year Adjustment Flag (Y or N) (Col. M)

I am trying to report summarized totals by customer, brand, G/L account, and period. I need to exclude any amounts that have Y in the prior year adjustment column and instead show those in the prior year totals. Each customer has their own worksheet which contains a section for each brand with lines for each G/L account and columns for each month (period). Details for these worksheets are:

Customer ID = AR6
Brand 1 = AQ7, Brand 2 = AQ43
Date For Period 1 = AS7, Period 1 Prior Year Adj. Col. = AT (not sure if I need this)
G/L Account 1 = AR8, G/L Account 2 = AR9, AR10, AR11 etc.

The formula I'm currently using (which is not resulting in an error but is providing no result at all) is:

{=SUM('CM''s 2-1-11 To 1-31-12'!$C$5:$C$1568=$AR$6)*('CM''s 2-1-11 To 1-31-12'!$F$5:$F$1568=$AS$7)*('CM''s 2-1-11 To 1-31-12'!$H$5:$H$1568=$AR8)*('CM''s 2-1-11 To 1-31-12'!$L$5:$L$1568=$AQ$7)*('CM''s 2-1-11 To 1-31-12'!$K$5:$K$1568)}

Help please? Any suggestions how I can make this work?

 

[Greg Truby]

Try resizing the formula to something with like ten rows...

{=SUM('CM''s 2-1-11 To 1-31-12'!$C$5:$C$15=$AR$6)*('CM''s 2-1-11 To 1-31-12'!$F$5:$F$15=$AS$7)*('CM''s 2-1-11 To 1-31-12'!$H$5:$H$15=$AR8)*('CM''s 2-1-11 To 1-31-12'!$L$5:$L$15=$AQ$7)*('CM''s 2-1-11 To 1-31-12'!$K$5:$K$15)}

And then use the Evaluate Formula tool to see what's going on.

[EDIT]

DOH!

It's SUMPRODUCT() not SUM(). No need to array enter. Geez!

 

[Stacy_Sutton]

I've never used SUMPRODUCT before. I tried looking at the formula evaluator and it looked like I was getting results. I'll try this in the morning (my time in CA) and see how it goes.

Thanks for your help!

 

[Aladin Akyurek]

Does "no result at all" mean that you see the formula itself in the cell or 0?

On Excel 2007 or later:

=SUMIFS(
    'CM''s 2-1-11 To 1-31-12'!$K$5:$K$1568,
    'CM''s 2-1-11 To 1-31-12'!$C$5:$C$1568,$AR$6,
    'CM''s 2-1-11 To 1-31-12'!$F$5:$F$1568,$AS$7,
    'CM''s 2-1-11 To 1-31-12'!$H$5:$H$1568,$AR8,
    'CM''s 2-1-11 To 1-31-12'!$L$5:$L$1568,$AQ$7,
    'CM''s 2-1-11 To 1-31-12'!$M$5:$M$1568,"<>Y")

On all versions...

=SUMPRODUCT(
    'CM''s 2-1-11 To 1-31-12'!$K$5:$K$1568,
    --('CM''s 2-1-11 To 1-31-12'!$C$5:$C$1568=$AR$6),
    --('CM''s 2-1-11 To 1-31-12'!$F$5:$F$1568=$AS$7),
    --('CM''s 2-1-11 To 1-31-12'!$H$5:$H$1568=$AR8),
    --('CM''s 2-1-11 To 1-31-12'!$L$5:$L$1568=$AQ$7),
    --('CM''s 2-1-11 To 1-31-12'!$M$5:$M$1568<>"Y"))

As Greg notes, when in doubt about the result of a formula, try to evaluate it on ranges of a reduced size.

 

[Stacy_Sutton]

No result at all means I'm seeing zero in the cell.

I'm off to try your tips and see what happens.

Thanks for your help!

 

[Stacy_Sutton]

Ok I used the SUMIFS and it's working!! Thank you so much for the support. I knew this could be done but it was just beyond my reach!

You guys have a great day!

 

[Aladin Akyurek]

Ok I used the SUMIFS and it's working!! Thank you so much for the support. I knew this could be done but it was just beyond my reach!

You guys have a great day!

You are welcome. Thanks for providing us feedback.

 

[Stacy_Sutton]

I overlooked the fact that I have users who are still on Excel 2003 so they are having trouble viewing the results of my SUMIF formulas. I need to change the formulas to SUMPRODUCT (and hope they'll still work).

Just to make sure... I omit the multiple occurrences of "--" in the example provided, right?

=SUMPRODUCT(
'CM''s 2-1-11 To 1-31-12'!$K$5:$K$1568,
--('CM''s 2-1-11 To 1-31-12'!$C$5:$C$1568=$AR$6),
--('CM''s 2-1-11 To 1-31-12'!$F$5:$F$1568=$AS$7),
--('CM''s 2-1-11 To 1-31-12'!$H$5:$H$1568=$AR8),
--('CM''s 2-1-11 To 1-31-12'!$L$5:$L$1568=$AQ$7),
--('CM''s 2-1-11 To 1-31-12'!$M$5:$M$1568<>"Y"))

Thanks again!

 

[Aladin Akyurek]

I overlooked the fact that I have users who are still on Excel 2003 so they are having trouble viewing the results of my SUMIF formulas. I need to change the formulas to SUMPRODUCT (and hope they'll still work).

Just to make sure... I omit the multiple occurrences of "--" in the example provided, right?

=SUMPRODUCT(
'CM''s 2-1-11 To 1-31-12'!$K$5:$K$1568,
--('CM''s 2-1-11 To 1-31-12'!$C$5:$C$1568=$AR$6),
--('CM''s 2-1-11 To 1-31-12'!$F$5:$F$1568=$AS$7),
--('CM''s 2-1-11 To 1-31-12'!$H$5:$H$1568=$AR8),
--('CM''s 2-1-11 To 1-31-12'!$L$5:$L$1568=$AQ$7),
--('CM''s 2-1-11 To 1-31-12'!$M$5:$M$1568<>"Y"))

Thanks again!

It should succeed as is. The -- bit does effect coercion from TRUE/FALSE values (results of equality or unequality evaluations) into 1/0's for SumProduct needs numbers to work.

Note that you can check this formula also on your 2007/2010 version in order to see that it works.