SumProduct

[Manny74]

How would I use a Sumproduct to calculate V7, W7, X7 &Z7? (For April)

I was able to use the Sumproduct for Jan, Feb, March, but am having trouble including April...

	V​	W​	X​	Y​	Z​
	Jan​	Feb​	Mar​	Q1​	Apr ​
7​	57.60%​	73.90%​	74.70%​	69.51%​	71.40%​
8​	22​	23​	31​	76​	29​

<tbody> </tbody>

Here is the formula I created for Jan thru March:


=SUMPRODUCT($V$7:X7,$V$8:X8)/SUM($V$8:X8)


= 69.51

 

[keelaboosa]

Why not put April in column Y and move the Q1 result to column Z? You'd then have SUMPRODUCT($V$7:Y7,$V$8:Y8)/SUM($V$8:Y8)

 

[Manny74]

Because I have to have 4 quarters in my report.

Plus I have several other rows with data which also require (contains) quarterly data....

If I move all 4 quarters to the end of the report, then I'd have to change the formulas on another long report, which is pulling data using HLookup.....

 

[jtakw]

Hi,

Assuming you have Headers in Row 6, or at least for Y6 showing Q1 as in your sample, does this give you what you want:

Book1
	V	W	X	Y	Z	AA
6				Q1	Apr
7	57.60%	73.90%	74.70%	69.51%	71.40%
8	22	23	31	76	29	0.700305
Sheet457

Cell Formulas
Range	Formula
AA8	=SUMPRODUCT((V6:Z6<>"Q1")*(V7:Z7)*V8:Z8)/SUMIF(V6:Z6,"<>Q1",V8:Z8)

 

[Manny74]

Wow, that's cool, thanks!
So would I still use "Q1" in the Sumproduct formula?
Since I would only be obtaining data from row 7 and 8....


V W X Y Z
6 Jan Feb Mar Q1 Apr
7 57.60% 73.90% 74.70% 69.51% 71.40%
8 22 23 31 76 29

 

[jtakw]

Yes, as you described above, you would use my formula "as-is".

 

[Manny74]

When I use that formula, I get 0.700305, so I converted it to a percentage ands it's 70.03?

 

[jtakw]

Are you saying that's Not the result you're looking for ?

Since you didn't explain the logic you're using,
My formula is based Strictly on you sample in OP (based on your current formula in Post # 1, but adding Column Z and excluding Column Y - "Q1")

So what's the Expected/Correct answer ? With explanation please.

 

[Manny74]

Sorry for not explaining...So I am trying to track scores....scores as a percent then the number of surveys for each of my users.

So quarterly, I can add up the number of surveys (row 8), but I am trying to figure out how to add percentages.

Besides using averages, I was told to use Sumproduct in my formula.....We use Sumproduct here but no one can show me or explain how..

So my data looks like this.....but again not sure how to use Sumproduct to add up percentages each month?

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

ROW 3

JAN

FEB

MAR

Q1

APR

MAY

JUNE

Q2

JULY

AUG

SEPT

Q3

OCT

NOV

DEC

Q4

ROW 7

57.60%

73.90%

74.70%

71.40%

68.80%

75.00%

75.80%

54.50%

83.30%

59.60%

72.70%

90.50%

ROW 8

22

23+22=45

31+45=76

76

29+76=105

31+105=136

38+136=174

174

22+174=196

11+196=207

12+207=219

219

19+219=238

11+238=249

7+249=256

37+256=293

<colgroup><col width="259" style="width: 194pt; mso-width-source: userset; mso-width-alt: 9472;"> <col width="64" style="width: 48pt;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="64" style="width: 48pt;" span="2"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" span="2"> <col width="64" style="width: 48pt;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" span="3"> <col width="64" style="width: 48pt;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" span="2"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <tbody> </tbody>

 

[jtakw]

So you haven't explained, what's the relationship between the % in row 7 and the numbers in row 8?
The % in row 7 is in relation to what, how is it calculated?
What are the expected/correct results for each month? ( Jan, Feb, Mar, Apr, May, etc. )