Request Help on SUMPRODUCt and scoring

[johnabrahambio]

Dear Members

I am trying to attempt at number of times a combination of text values appear and if the combination apprears want to give a score accordingly

I tried using SUMTOTAL. In some rows it worked and in some it didnt . I am not able to figure it out why it is notworking. Request dear member help on where I did wrong

The rows with wrong results are filled in RED (c7 and e7). Request members if the approach is also right or is it any other better way to do it. In G6 I want to give score as per the combination number taking the value from the master score table.How Can I do it for all the rows from requirement summary table.Please see the attached sheet

Request to please help

Thanks
John

[canapone]

Hi,

in sheet IAA "Critical" is spelled "Critical " (one final blank space).

Try to adjust sumproduct in C7 and below as

=sumproduct((IAA!C$2:C$11="OOTB")*(IAA!B$2:B$11="Critical ")*(IAA!A$2:A$11="Current"))

Same correction for "Customization " in E11 and below.

Hope that helps.

Regards

[johnabrahambio]

Thanks for the reply.I am on MS office 2010 and I dont see any gaps. Confused

[canapone]

Hi (I'm using Excel 2000),

please see the file attached if it helps.

In alternative, you could try in E2 sheet IAA

=trim(a2)

copy the formulas in E2:G11 and copy with paste special values what you get from the formulas on A2:A11.

In this case you do not need to adjust your sumproducts.

Regards

[johnabrahambio]

Thanks a lot for the help. But why does it takes gap some times and doesnt at other times?

Request if you can help on calculating scores based on the master score table

Thanks
John

[canapone]

Hi, I've just reviewed a couple of formulas from your file.

Maybe a better strategy is to eliminate with TRIM final blank spaces from IAA database.

Regards

[johnabrahambio]

Please let me know how to use that TRIM. I am a beginner in excel

Thanks
John

[canapone]

Hi,

see the attached file.

Regards

[johnabrahambio]

Got it now thanks. But the problem I have is that I have numerous such sheets and adding TRIM each of the those sheets is really time consuming

Thanks
John

[canapone]

Hi,

you could also select the data (column A-C sheet IAA) , then click on Edit / Replace and in Find what just enter a space then click Replace all.

Regards

[johnabrahambio]

Great .It is working. Please help how do automate deriving final score based on the text selection and scores gives in master score table

Thanks
John