# SUMIF with unknown criteria

1. ## 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.

2. ## Re: SUMIF with unknown criteria

I worked with your simplified version. The formula for Front Paths/Other is

=SUMIFS(\$F\$3:\$F\$5,\$C\$3:\$C\$5,"<>TARMAC",\$C\$3:\$C\$5,"<>CONCRETE")

for Rear Paths/Other

=SUMIFS(\$F\$9:\$F\$11,\$C\$9:\$C\$11,"<>TARMAC",\$C\$9:\$C\$11,"<>CONCRETE")

SUMIFS allows you to specify multiple conditions to be met for inclusion in the sum, and all conditions must be met.

3. ## Re: SUMIF with unknown criteria

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.

4. ## Re: SUMIF with unknown criteria

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

There are currently 1 users browsing this thread. (0 members and 1 guests)