Variation on SUMIFS

[ychartra01]

OK I am using a sumifs formula to add up records in a table that meet several criterias (i.e. Colour, warehouse, style, source of demand). I linked the formula to validated fields. So when I change the colour the numbers re-calculate based on the new colour.

For Branch and colour though, I would like to have the option to specify all (i.e all colours). Is there a way to write a SUMIFS formula where a condition can be ignored based on input from a cell linked to a list of values?

Thanx, in advance.
YCHARTRA

[Palmetto]

Upload a sample workbook so that we can better understand what you want.

[DonkeyOte]

Yes, you can utilise wildcard approach (in conjunction with clearing the validation selection (ie delete cell content so it reverts to blank))

Please Login or Register  to view this content.

Would SUM C1:C1000 where A range = D1 value "plus anything" and B range = E1 value "plus anything" ... so it follows that if D1 is Apple and E1 is Banana it will look for "Apple*" in A and "Banana*" in B ... were D1 and/or E1 blank it would look for any value at all - ie "*" ... if D1 were A it would look for "A*" etc...

Pending the values in your own file it may be that you will need to adapt the above approach by means of embedded IF if wildcard entry not watertight, eg:

Please Login or Register  to view this content.

If the above do not work for you then as Palmetto has already stated post a sample file that reflects your setup.