SUMIF with unknown criteria

Hi,

I have a sheet with a survey form where I need to sum the quantity of a repair material where I don't know in advance of getting back the surveys from the surveyors, what all the repair materials are. On the sheet there are two tables containing this data which show the material that footpaths and driveways are constructed of, and therefore what they need to be repaired with. In the majority of cases it's going to be either Tarmac or Concrete, and I'm using the formulas

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

to sum the total quantity of each material in a table that summarises the quantities of materials required.

Where the repair material is neither Tarmac or Concrete though, I want to capture the quantity of the material needed (whatever the material is) in a column called 'other'

I've attached both the original survey sheet, and for clarity a simplified sheet showing the source table and the summary table. In essence what I'm trying to do is say 'if the repair material is Tarmac or Concrete, sum the quantity of each required, but if the repair material is something else, sum the quantity of that'. In the simplified example I've attached, I'd therefore expect Excel to sum the quantity of 'gravel' and 'block paving' as shown in green on the example. If cells in the ranges C3:C5 and C9:C11 are empty obviously there wouldn't be anything to sum.

I hope that makes sense, and any assistance appreciated.

Thank you so much. That works perfectly! I've also now used this formula with the appropriate cell references to do the same thing with driveways, which has been bugging me for weeks, trying to think of the best way to capture the odd property with a driveway which isn't tarmac or concrete, so that's solved two issues in one go.

Thanks again for your help!

You're welcome! Thanks for the rep, and thanks for marking your thread Solved.