Using SUMPRODUCT to create a weighted score

[mflatt23]

Hello everyone,

I've been working on a stock analysis spreadsheet and am getting a little stuck trying to create a weighted score. In the spreadsheet linked below, I've got a couple of columns:

- Answer (this is a "Y" or "N")
- Score (if "Y," then 1; if "N," then 0)
- Weight (either 1 or 2..might change these up later)

In cell C49, I've got the Total Score as a percentage of "Y" answers. I'd like to integrate a Weighted Score in cell D49, but I'm getting stuck using the SUMPRODUCT formula to make it a percentage like Total Score.

Any help/direction would be much appreciated!

Spreadsheet link: Upload files for free - Stock Research.xlsx - ufile.io

 

[StephenCrump]

Try:

	A	B	C	D
1
2		Answer	Score	Weight
3		N	0	2
4		Y	1	1
5		Y	1	1
6		Y	1	2
7		Y	1	1
8		Y	1	2
9		Y	1	1
10		N	0	1
11		Y	1	2
12		Y	1	1
13		Y	1	2
14		Y	1	1
15		Y	1	1
16		Y	1	1
17		Y	1	2
18		Y	1	2
19		Y	1	1
20		Y	1	1
21		Y	1	1
22		Y	1	1
23		Y	1	1
24		Y	1	1
25		Y	1	1
26		Y	1	1
27		Y	1	1
28		Y	1	1
29		Y	1	1
30		Y	1	1
31		Y	1	1
32		Y	1	2
33		Y	1	1
34		Y	1	1
35		Y	1	1
36		Y	1	1
37		Y	1	1
38
39
40		Weighted score	93.0%
41		OR	93.0%
42
Sheet1

Cell Formulas
Range	Formula
C3:C37	C3	=IF(B3="N",0,1)
C40	C40	=SUMPRODUCT(C3:C37,D3:D37)/SUM(D3:D37)
C41	C41	=SUMPRODUCT(--(B3:B37="Y"),D3:D37)/SUM(D3:D37)

 

[mflatt23]

Boom! Thank you. I did a COUNTA instead of SUM. Much appreciated