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