• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Possibly beyond the capabilities of excel!!!


New Member
I am trying to create a formula that I am afraid is beyond the scope of excel. My first question is - Is it beyond the scope? and second - Do you have any suggestions as to what to use for what I am doing?(ie. crystal reports, jreports, etc.)

I have a worksheet that has multiple fields to track sales activity. I am trying to create reports that follow 3 main criteria but that material is variable. I have inserted an additional worksheet that has the 3 criteria fields as drop downs. Depending on what I select as the drop down is how I would like to create the results of the data.

So the 3 drop downs are "Salesperson", "Type of Sale", "Source". It is set up for an insurance business - just info to add clarity to my example.

The data that I am trying to compile works like this. If no drop downs are selected the formula shows all of the sales pitches and respective closes. However, if I select a specific Salesperson it will only show data from them (I have this part working so far). What I don't have working is to be able to show how many pitches and closes there are of a specific "Type of Sale" (ie. Auto Insurance), and then to further drill down as to the "SalesPerson", how many "Autos" from the "Yellow Pages".

What I had in mind was the ability to use any or none of the drop downs simultaneously. So I can view all pitches and closes from a single source, of a specific type, a specific salesperson, or a combination of all 3.

eg. Bob has 10 pitches and 5 closes. Bob has 4 pitches and 2 closes if auto is selected. Bob has 1 pitch and 0 closes if auto, and yellow pages is selected. If yellow pages is selected alone (without a salesperson or type) it shows 22 pitches and 8 closes.

Essentially it is a formula that filters down the data based on additional criteria not being met.

Any ideas will help.

Thanks in advance.


New Member
I just created a simple formula that does almost the entire function.


The C2, E2, and F2 fields are drop downs of all of the selections that may exist. The one missing element is if I wanted to leave one of those (C2, E2, or F2) blank in order to yield cumulative results instead of specific (which I want to be able to do). In other words right now I have to choose a specific salesperson, type, and source. But if I wanted to see Auto pitches, from the Yellow Pages accross all salespeople it won't work.

I have been trying to nest a IF(ISBLANK formula within but can't seem to make it work.

eg. =SUMPRODUCT((C9:C3004=C2)*(E9:E3004=E2)*(F9:F3004=F2,(IF(ISBLANK(F2)COUNTIF(F9:F3004,"*")))))

I would have to add the IFBLANK to all three criteria but have only shown my idea for one of them.

Any thoughts??




Excel Ninja
Staff member

I would always use either named ranges when using Sumproduct or Absolute references

ie: =SUMPRODUCT(($C$9:$C$3004=C2)*($E$9:$E$3004=E2)*($F$9:$F$3004=F2))

I have seen lots of cases where excel shifts the ranges when they are not absolute, I don't know why, but I know Absolute Ranges fixes it

For the Blank queries you could put some cells somewhere with the four combinations of possible answers ie: All Have values, and 1 cell for each being blank

Each cell would have an equation approriate for if C@ is Blank, E2 is Blank, F2 is Blank and No Blanks

Then use a Choose function to select which one you want to use.

eg: =+choose(if(c2=blank,1,if(E2 =blank,2,if(F2=blank,3,4))), Formula for C2 Blank, Formula for E2 Blank, Formula for F2 Blank, Formula for no Blanks)