Adding up numbers that have blank celssmatching criteria in 1 or 2 ranges across 3 tables

Occasional Visitor

Hi All

 

I am trying to add up costs not captured using other formulas in an estimating tool.  I have 3 tables (Resources, Non-resources, and Other Costs - I put them in separate tables to be dynamic for the user to expand it).  The estimated cost of each line is added up based on cost type and phase and put in the summary section at the top (E1:K10).  I want to also capture costs not categorised by Cost Type and/or Phase across all tables.

 

If a cell in any of the Cost Type (column A - blue) fields or Phase (column B - green) fields across all tables is blank, I want the formula to identify that and add up the cost in the Estimate Total (column K - red) fields and put it in the Uncategorised cell (F10 - yellow).

 

This will show up any costs identified in the tables by the user that have not been categorised into Capex or Opex and a phase selected, therefore left out of the total cost estimate. 

 

Here's a screenshot:

Fiona_Murphy_1-1649225927717.png

 

Any suggestions appreciated.  I'm an average user .

1 Reply

@Fiona_Murphy Perhaps the easiest way would be to calculate the grand total and deduct the the sum of the expenses that are categorized. So in F10 it would be something like:

 

= SUM(K14:K1000)-F2-F6

 

But even better when you use structured tables to avoid direct referencing to a range like K14:K1000, or whatever is needed to capture all expenses.