Streamlining a formula

**c.drysdale89** · 05-09-2012, 12:15 PM

Below is a formula that is being used in a spreadsheet I have taken over in my new job:

=IF(ISNA(SUM(VLOOKUP(A9,'BF ProjectBreakout'!A:O,7,0),(VLOOKUP(A9,'BF ProjectBreakout'!A:O,12,0)))),0,(SUM(VLOOKUP(A9,'BF ProjectBreakout'!A:O,7,0),(VLOOKUP(A9,'BF ProjectBreakout'!A:O,12,0)))))

It is showing the total sum of specific departments, within a specific project - BF ProjectBreakout consists of the pivot table where the data comes from.

I just wondered if there could be a better way to write this formula - perhaps incorporating 'MATCH' for the column names in case the number of columns moved each week?

If anyone can help that would be great, or if more information is needed (which I am sure it will) let me know.
Thanks
Claire

**Cutter** · 05-09-2012, 12:23 PM

Step 1 (if your employer is using Excel version 2007+ and backward compatibility is not an issue) is to replace IF(ISNA()) so your current formula could be:

=IFERROR(SUM(VLOOKUP(A9,'BF ProjectBreakout'!A:O,7,0),VLOOKUP(A9,'BF ProjectBreakout'!A:O,12,0)),0)

anything further requires more info.

**Pepe Le Mokko** · 05-09-2012, 12:24 PM

Does this help ?

Please Login or Register  to view this content.

**Haseeb Avarakkan** · 05-09-2012, 09:36 PM

Or,

=IFERROR(SUMPRODUCT(VLOOKUP(A9,'BF ProjectBreakout'!A:O,{7,12},0)),0)

Or,

Enter heading in A8 as same heading in column A of BF ProjectBreakout sheet, then use DSUM

=SUM(DSUM('BF ProjectBreakout'!A:O,{7,12},A8:A9))

**c.drysdale89** · 05-10-2012, 04:49 AM

Here’s a little more information:

Tab named ‘BF ProjectBreakout’ contains a pivot table showing how much time has been added to each project, within each department, for example:

untitled.GIF

On another tab I am trying to show what time has been burnt dependent on the project code (or number) and their department. HOWEVER, as an example, TEC and MOB are one overall department and JSX is another.

As I have already shown, this is my original formula =IF(ISNA(SUM(VLOOKUP(A9,'BF ProjectBreakout'!A:O,7,0),(VLOOKUP(A9,'BF ProjectBreakout'!A:O,12,0)))),0,(SUM(VLOOKUP(A9,'BF ProjectBreakout'!A:O,7,0),(VLOOKUP(A9,'BF ProjectBreakout'!A:O,12,0))))) but this is only if the columns stay exactly the same each week, which may not be the case…

So, could anyone suggest a slightly better formula, perhaps taking ‘match’ into account? Or would the ones suggested be the best…?

Thanks!
Claire