Excel Sumproduct Query 2

[Exceln00b]

can someone tell me if this is a wrong usage?

=IF(B6="EM";SUMPRODUCT(--(E8='[Raw Data.xls]Sheet1'!$I:$I)+('[Raw Data.xls]Sheet1'!$CX:$CX="10"));SUMPRODUCT(--(B6='[Raw Data.xls]Sheet1'!$L:$L)*--(E8='[Raw Data.xls]Sheet1'!$I:$I)*--('[Raw Data.xls]Sheet1'!$CX:$CX="10")))

It returns a #num! value.

any idea how i can correct the same?

Thanks in advance guys!!

 

[SkipVought]

Hi,

You cannot use SUMPRODUCT on an entire column reference. Limit the range to the actual rows of DATA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Exceln00b]

dang! that worked like a charm.
Thanks a TON!!!!


well, probably i should start a new thread, but since the question is related to the previous formula, I am hoping that you can provide a better, easier and less complex formula.

So here goes :

I have two files, one which contains the Raw data and the other where i need to display the results from the Raw data.

The Raw data file contains Labels and corresponding data with regards to : Date(month wise), Country, Parameters(Various).
The parameters are basically ratings on the scale of 0-10. A rating of 0-4 is considered as Dissatisfied, 5-8 is considered as Satisfied and 9-10 as extremely satisfied (for eg: how customers rate a particular feature like 'Ease of finding Info')

Now, on the excel file i want to display the results, I have a drop down list which contains country names (corresponding to the ones in the Raw file). What i intend to do is , when one selects the country name from the list, the formula should search for the Date (specified on the results page in any cell) in the raw data file, compare the name of the country for match (in the raw data file, again) and do a count of the 10's, 9's etc and display the same.

For example, in this formula
=IF(B6="EM";SUMPRODUCT(--(E8='[Raw Data.xls]Sheet1'!$I2:$I3655)+('[Raw Data.xls]Sheet1'!$CX2:$CX3655="10"));SUMPRODUCT(--(B6='[Raw Data.xls]Sheet1'!$L2:$L3665)*--(E8='[Raw Data.xls]Sheet1'!$I2:$I3655)*--('[Raw Data.xls]Sheet1'!$CX2:$CX3655="10")))

B6="EM" is on the display file and is for the result of all countries, so if the selection is NOT EM in the display page, then it will check for the country name, then compare the DATE which is in E8 and then gives the count of 10s in the row CX.

Any better solutions for this formula?? if i use Vlookup will it become simpler? if yes hoa can i use it? ny suggestions and help is much appreciated!

cheers

 

[SkipVought]

Please post a sample of your source data.

Your example has ([Raw Data.xls]Sheet1'!$CX2:$CX3655="10")

Your requirement states, "9-10 as extremely satisfied" which would be...
[tt]
(range>=9)*(range<=10)
[/tt]
Actually, I'd be putting the satisfaction values in cells and referencing the cells in the formula -- ONE formula for ALL satisfaction ranges


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Exceln00b]

Thanks Skip, your hint has sparked another idea,

anyway, am attaching the files, Raw D.xls is the raw data file (I have deleted many of the fields and have just kept a few so that you would get an Idea)

The other File is Display.xls where I plan to display the results.

have attached a copy as rar.
Really appreciate for your help!!!

 

[SkipVought]

I can't seem to download your file.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Wel I finally downloaded the .rar file, but I have nothing to open it with!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

You need winrar for that Skip ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

They've clampled down on what we can load on our PCs.

Sorry, I cannot open.

You can e mail to me

1136250 at bellhelicopter dot textron dot com

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Exceln00b]

Mailing you the file,Skip

you are kind and I really appreciate your help.

IOU

 

[Exceln00b]

oopsss...

the mail failed to deliver. Am attaching the link here

http://www.mediafire.com/file/myn40nz1zln/Display.xls

http://www.mediafire.com/file/qzm3mwmjzmz/Raw

D.xls

Thanks again.

Please let know if you have issue viewing the file.

 

[SkipVought]

OK I have both files open.

Where do we start?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Exceln00b]

here ya go.

k, now display is where I want the data displayed. The count of the 10's, 9's should be displayed against the list with colour coding (10-0)

This is based on a monthly cycle (the month is displayed in E8 - K8)

And the count displayed, depends on the drop down menu in B6.

I do have the formula in the row and columns from E24-K24 and E34 to K34.

that might give you the picture.

(you can neglect the top part of the file which shows the sample size and percentage data, avg importance,satisfaction and gap (once the data is pulled correctly in E24 to K34 then its easy to populate the rest)

am i any clear? :S

 

[SkipVought]

Please state a specific question. I don't know where you want to go with this. You're focused. I'm not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

General comments:

You ought to be using REAL DATES. Rather, you are using LABLES 'Nov-07, which in many cases are virtually useless except for lables. They will not sort correctly, let alone be able to be used in calculations.

You have formulas that have an absolute reference that ought to be absolute to the row and relative to the column. You have make this harder than it needs to be.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Exceln00b]

oops sorry,

I do have a solution for the query, but its long winding.

the formula that you see in the cells, E24-K34 of the Display.xls file does the calculation and is the fix.

What am trying to do is to find an easier, less cumbersome formula to get the same result.

to be clearer, am trying to display the count of 10s -0's FROM the Raw data file, for , say 'Satisfaction (Ease of Finding Information)which is in H1 of Raw D.xls.

But on the display.xls the user chooses which country's data to view by clicking down the menu in B6.

So if he selects a country, the file has to check for the corresponding month and the country in the raw file and display the count of the rating in the rows E24-K34.

If you keep both files open, and drop down the menu and select a country, u will see that the data changes.

My issue is that I will have more than one parameter like 'Ease of Finding Information' to display and this formula takes a long time to calculate and is quite complex for someone else to understand.

is there a simpler way to do this?

 

[Exceln00b]

my response is below:


General comments:

1: You ought to be using REAL DATES. Rather, you are using LABLES 'Nov-07, which in many cases are virtually useless except for lables. They will not sort correctly, let alone be able to be used in calculations.

Ans: Well the raw data i download from a tool, has limitations, so i convert the 'date format' in the actual download to Text, which is then compared with labels in E8 to K8 to see if theres a match and then pull the corresponding data for a count.


2: You have formulas that have an absolute reference that ought to be absolute to the row and relative to the column. You have make this harder than it needs to be.

Ans: Part of what Iam trying to fix, but i could only make Display.xls with the menu using SUMPRODUCT and, as you mentioned in the begining of the post, it doesnt work otherwise


:s

 

[SkipVought]

1) Change the Satisfaction headings in Raw D to Satisfaction1 & Satisfaction2

2) Name the ranges in Raw D using Insert > Name > Create Name - Create Nmaes in TOP row.

3) In Display, name B6 SelectedCountry

4) In Display, on sheet2, enter this list as a named range
[tt]
Categories
Ease Of Finding Information
Website Speed
[/tt]
5) In Display, make C12 a Data > Validation --LIST using Categories as the list name.

6) In Display, Name B12 SelectedCat

7) formula in E24
[tt]
=IF(SelectedCountry="EMEA",SUMPRODUCT(--(E$8='Raw D2.xls'!Cycle_Date)*--(INDIRECT("'Raw D2.xls'!Satisfaction"&SelectedCat)=$C24)),SUMPRODUCT(--(SelectedCountry='Raw D2.xls'!country)*--(E$8='Raw D2.xls'!Cycle_Date)*--(INDIRECT("'Raw D2.xls'!Satisfaction"&SelectedCat)=$C24)))
[/tt]
If this takes too long to calculate using your full range of data, consider using MS Query to return summary data for each country. The do your lookups in that table instead.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Please note that I saved raw d as raw d2 when I changed the headings.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}