Possibly beyond the capabilities of excel!!!

[arivlin]

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.

 

[arivlin]

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


=SUMPRODUCT((C9:C3004=C2)*(E9:E3004=E2)*(F9:F3004=F2))


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??


Thanks.


Alex

 

[Hui]

Arivlin


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)