Multiple Criteria Formula

With Excel 2003, you need SUMPRODUCT.

For example:

=SUMPRODUCT(--($I$8:$I$46="DIRECT"),--($L$8:$L$46=F$48),--($E$8:$E$46))


Regards

In F52

=SUMPRODUCT(($E$8:$E$46)*($I$8:$I$46="Direct")*($L$8:$L$46=F$48)*($O$8:$O$46="PENDING (95%)"))*.95+SUMPRODUCT(($E$8:$E$46)*($I$8:$I$46="Direct")*($L$8:$L$46=F$48)*($O$8:$O$46="VERY LIKELY (75%)"))*.75

Just for the first 2 %'s. To complete it you need to add the other 3 so you can see how long the result would be.


EDIT - I edited the above formula because I forgot to put in the % adjustments (*.95 and *.75)

If it were me, yes, I'd make a few changes ;-)

1. Split the data input/collection and the analysis into two separate sheets. You then don't need to worry about aligning cells containing different types and lengths of data. It also makes it easier to add data at the bottom of the list rather than inserting rows (and wondering if your formulae still include all the data).

If you have more than one type of analysis, I'd put them on different sheets ... same reasons.

If you need to print all the data, just select all the sheets and print them ... and they will automatically start new pages for each sheet which, in my opinion, makes it easier to follow (but wastes some paper)

2. I'd remove all merged cells ... really bad news

3. I'd remove all blank columns ... yes, you can make the layout look pretty but it can be a pain for sorting and/or filtering.

4. I'd try and only have one header row on the data input ... makes it easier if you want to create dynamic ranges. Not a big issue though given the format you have.

5. I'd split the Categories into three columns to reflect the options in the drop down boxes ... that way you can use those cells in the SUMPRODUCT rather than using text values. This makes it easier to have "common" formula rather than specifics depending on the row.

You can also use the status to look up (VLOOKUP) the percentage for calculations.

6. If your clients are regulars, I'd have another sheet listing client names and details, for example contacts, addresses, zip codes, whatever.

You can use the client names in another drop down box which is good for consistent input of client names because, have no doubt, there will come a point where you also want to analyse the sales by client and/or location to determine the value, effort and consistency.

Even if you're getting new clients, I think it still makes sense to add them to a client list for subsequent re-use and analysis.

7. Depending on how long a period you are going to be inputting data, it might be useful to have a date column. It's one of those things ... if you don't put it in from the start, it's a pain to do later when you decide you want an analysis by day, week or month, etc.

So, a few thoughts ... I hope it doesn't sound picky, but you did ask ;-)

Let me know if you need any clarification.

Regards