1

I am trying to sum a range based on a few conditions.

When I enter

=SUM(IF((INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N))/INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N)),0))

the formula returns a valid result.

However, when I add the condition

LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When"

as follows:

=SUM(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N))/INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N)),0))

then I get the #N/A error.

Even when I remove the division operation, e.g.

=SUM(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNT(CommissionDetail!$N:$N)),0))

I still obtain the #N/A error.

When I do a count in column Z as follows:

=COUNT(IF((LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),SEARCH("(",INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)))-2)="As and When")*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>0)*(INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))<>""),INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNT(CommissionDetail!$N:$N))))

I do get a valid result, so I know that there are at least some cells that meet the condition I mentioned above.

Why, then, would I get the #N/A error when summing?

I am specifically interested in understanding why the formula ceases being valid once a condition is added that is satisfied at least more than once and that works in the COUNT formula.

The end objective is to determine the average as-and-when commission charged per month on a product. I removed the conditions to split commission by month to avoid over-complicating the already confusable formulae above. The justification for using these array formulae is that they are dynamic and are calculated only once per month, and need to be transferrable to new workbooks monthly by users who are unable to add helper columns or interpret the workings of the formulae. Using helper columns brings me to my goal, but unfortunately helper columns are not options in this instance

Any ideas?

0

1 Answer 1

2

In the first formula you used COUNT, i.e.

COUNT(CommissionDetail!$N:$N)

but in the additional part you used COUNTA, i.e.

COUNTA(CommissionDetail!$N:$N)

If there are any text values in column N (e.g. header row) then those won't give the same number (hence #N/A error) because COUNT only counts numbers, COUNTA counts all entries.

I suggest you change to COUNTA throughout because using COUNT will ignore the last row if you have a header row

You can probably also simplify a little, don't think you need the SEARCH part in the new condition, why not just look for cells that start with As and When (.....and you could use IFERROR rather than checking for blanks or zeroes as divisors, i.e. including new condition (and COUNTA throughout as suggested) that would be:

=SUM(IFERROR(IF(LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&COUNTA(CommissionDetail!$N:$N)),13)="As and When (",INDIRECT("CommissionDetail!$AF$2:$AF$"&COUNTA(CommissionDetail!$N:$N))/ INDIRECT("CommissionDetail!$Z$2:$Z$"&COUNTA(CommissionDetail!$N:$N))),0))

confirmed with CTRL+SHIFT+ENTER

....and a further improvement to make the formula more readable is to define your repetitious element, i.e.

=COUNTA(CommissionDetail!$N:$N)

as a named range, e.g. RowCount and then the formula becomes shorter again, i.e.

=SUM(IFERROR(IF(LEFT(INDIRECT("CommissionDetail!$AL$2:$AL$"&RowCount),13)="As and When (",INDIRECT("CommissionDetail!$AF$2:$AF$"&RowCount)/ INDIRECT("CommissionDetail!$Z$2:$Z$"&RowCount)),0))

3
  • Thank you for pointing out the COUNT vs. COUNTA error - this definitely was the cause of my specific error since it resulted in a mismatch between the ranges.
    – Aaa
    Sep 4, 2013 at 18:19
  • Thanks for pointing out the shorter method for identifying commission types - sometimes it requires a fresh set of eyeballs to spot inefficiencies like that. I try to veer away from using named ranges as I do not want to instruct the user to ensure names are kept consistent after copy/pasting or copying whole workbooks, but thanks for the suggestion. I avoided IFERROR(...,0) as I was working with the AVERAGE formula which would have its result affected by it.
    – Aaa
    Sep 4, 2013 at 19:05
  • I don't think using IFERROR(...,0) in this formula will cause problems because you are summing so the zero makes no difference to the result - if you are averaging you can change to IFERROR(....,"") and the text value "" will be ignored..... Sep 4, 2013 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.