Help extending automatic (indirect) formula

[arm1e]

Hello

I have been working on a formula which will sum all of the numbers for a set of cells whose column and row identifiers are automatically calculated to match a set of criteria inputted into some other cells.

I have this working but I want to roll it out to other sheets (same design) where the number of cells to be summed will be different. As this means some of the INDIRECT() references are now blank, it throws up an error since the result of the indirect function returns N/A, which cannot be summed. This is fixable if I remove these statements from the overall formula, but this defeats the purpose of being able to have a single formula that fits all.

Is there anything that can be done to improve this?

Here is the code:

Please Login or Register  to view this content.

The idea here is that:
- column AH is where I input the content so, if it is blank then it would add nothing to the sum.
- column AJ contains a lookup of the matching column, eg $M
- if either AH is blank, the blank in column AJ will add nothing to the sum (ie, ignoring it)

I know that I can use SUM , which I did have but wondered if it was what was causing the error and have not had time to change it back yet

Thanks for any help

[AliGW]

Welcome to the forum.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[jason.b75]

With a sample file where we could see the formulas in AH and have some data to work with, it might be possible to simplify the formula.

With what you have given us so far, you could either replace the double quotes with zero's or enclose the whole thing in SUM() and replace the plus signs with commas.

[arm1e]

Sorry guys,

Here is the sample file. The column positions have changed but the rest is the same. I have removed the names column to protect the inocent

[arm1e]

Brilliant!! I replaced the quotes with 0's and it works. Only problem now is how to calculate the value in column B which errors if there are blanks.

[jason.b75]

Try this formula in A5 of your sample file and fill down, see if it helps.

Please Login or Register  to view this content.

edit:-

For the MAX in column B, try

Please Login or Register  to view this content.

That should allow you to remove the INDIRECT formulas and the ADDRESS formulas in columns Y and Z

[arm1e]

How did you do that?????
Please can you walk me through the function as it is WAY simpler than what I spent 3 hours on

[jason.b75]

In normal use, SUMIF looks at a list of items (D3:V3 in your sample sheet) and compares them to a specifed criteria (X5). If any item(s) in the list match the criteria, then the corresponding value(s) in the sum range (D5:V5) are totalled.

SUMIF as a single function only works by comparing a single criteria to the list of items, nesting it in SUMPRODUCT allows multiple criteria (the second list in X5:X11) to be used, giving the cumulative total as the end result.