My formula works fine... but is rather lengthy. Is there a short way to express it? See below, it's basically adding categories (criteria) of any "Blue" and any "Orange" and any "Yellow" text in a given range on another worksheet (that's the INDIRECT) and adding the 3 together. I need the total even if here are not any or one or two of the categories.
Formula:Please Login or Register to view this content.
BONUS QUESTION:
You see those 3 extra spaces in my criteria, i.e. "<space><space><space>BLUE"? That's how the spreadsheet gets imported, and while I know the formula to remove those spaces, it'll be too complex to implement on my worksheets, I think. I have over 100 worksheets in the workbook, each with 40-50 of the criteria with the spaces. Luckily - they are are uniform, 3 spaces exactly, but it's a pain making sure my criteria formula is correct. If I miscount a space in my formula, it would mess everything up. Is there a way to write the formula above to ignore spaces?
Bookmarks