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...


[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 21, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]

V​

[/TD]
[TD="width: 64, bgcolor: transparent"]

W​

[/TD]
[TD="width: 64"]

X​

[/TD]
[TD="width: 64, bgcolor: transparent"]

Y​

[/TD]
[TD="width: 64, bgcolor: transparent"]

Z​

[/TD]
[/TR]
[TR]
[TD="width: 21, bgcolor: transparent"][/TD]
[TD="width: 64"]

Jan​

[/TD]
[TD="width: 64"]

Feb​

[/TD]
[TD="width: 64"]

Mar​

[/TD]
[TD="width: 64"]

Q1​

[/TD]
[TD="width: 64"]

Apr
​

[/TD]
[/TR]
[TR]
[TD="width: 21, bgcolor: transparent"]

7​

[/TD]
[TD="width: 64"]

57.60%​

[/TD]
[TD="width: 64"]

73.90%​

[/TD]
[TD="width: 64"]

74.70%​

[/TD]
[TD="width: 64"]

69.51%​

[/TD]
[TD="width: 64"]

71.40%​

[/TD]
[/TR]
[TR]
[TD="width: 21, bgcolor: transparent"]

8​

[/TD]
[TD="width: 64"]

22​

[/TD]
[TD="width: 64"]

23​

[/TD]
[TD="width: 64"]

31​

[/TD]
[TD="width: 64"]

76​

[/TD]
[TD="width: 64"]

29​

[/TD]
[/TR]
</tbody>[/TABLE]


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?



[TABLE="width: 1084"]
<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>[TR]
[TD="width: 259, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]V[/TD]
[TD="width: 107, bgcolor: transparent"]W[/TD]
[TD="width: 64, bgcolor: transparent"]X[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[TD="width: 70, bgcolor: transparent"]Z[/TD]
[TD="width: 77, bgcolor: transparent"]AA[/TD]
[TD="width: 77, bgcolor: transparent"]AB[/TD]
[TD="width: 64, bgcolor: transparent"]AC[/TD]
[TD="width: 77, bgcolor: transparent"]AD[/TD]
[TD="width: 77, bgcolor: transparent"]AE[/TD]
[TD="width: 77, bgcolor: transparent"]AF[/TD]
[TD="width: 64, bgcolor: transparent"]AG[/TD]
[TD="width: 77, bgcolor: transparent"]AH[/TD]
[TD="width: 77, bgcolor: transparent"]AI[/TD]
[TD="width: 70, bgcolor: transparent"]AJ[/TD]
[TD="width: 77, bgcolor: transparent"]AK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] ROW 3
[/TD]
[TD="bgcolor: transparent"]JAN[/TD]
[TD="bgcolor: transparent"]FEB[/TD]
[TD="bgcolor: transparent"]MAR[/TD]
[TD="bgcolor: transparent"]Q1[/TD]
[TD="bgcolor: transparent"]APR[/TD]
[TD="bgcolor: transparent"]MAY[/TD]
[TD="bgcolor: transparent"]JUNE[/TD]
[TD="bgcolor: transparent"]Q2[/TD]
[TD="bgcolor: transparent"]JULY[/TD]
[TD="bgcolor: transparent"]AUG[/TD]
[TD="bgcolor: transparent"]SEPT[/TD]
[TD="bgcolor: transparent"]Q3[/TD]
[TD="bgcolor: transparent"]OCT[/TD]
[TD="bgcolor: transparent"]NOV[/TD]
[TD="bgcolor: transparent"]DEC[/TD]
[TD="bgcolor: transparent"]Q4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] ROW 7
[/TD]
[TD="bgcolor: #E2EFDA"]57.60%[/TD]
[TD="bgcolor: #E2EFDA"]73.90%[/TD]
[TD="bgcolor: #E2EFDA"]74.70%[/TD]
[TD="bgcolor: #E2EFDA"] [/TD]
[TD="bgcolor: #E2EFDA"]71.40%[/TD]
[TD="bgcolor: #E2EFDA"]68.80%[/TD]
[TD="bgcolor: #E2EFDA"]75.00%[/TD]
[TD="bgcolor: #E2EFDA"] [/TD]
[TD="bgcolor: #E2EFDA"]75.80%[/TD]
[TD="bgcolor: #E2EFDA"]54.50%[/TD]
[TD="bgcolor: #E2EFDA"]83.30%[/TD]
[TD="bgcolor: #E2EFDA"] [/TD]
[TD="bgcolor: #E2EFDA"]59.60%[/TD]
[TD="bgcolor: #E2EFDA"]72.70%[/TD]
[TD="bgcolor: #E2EFDA"]90.50%[/TD]
[TD="bgcolor: #E2EFDA"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] ROW 8
[/TD]
[TD="bgcolor: #E2EFDA"]22[/TD]
[TD="bgcolor: #E2EFDA"]23+22=45[/TD]
[TD="bgcolor: #E2EFDA"]31+45=76[/TD]
[TD="bgcolor: #E2EFDA"]76[/TD]
[TD="bgcolor: #E2EFDA"]29+76=105[/TD]
[TD="bgcolor: #E2EFDA"]31+105=136[/TD]
[TD="bgcolor: #E2EFDA"]38+136=174[/TD]
[TD="bgcolor: #E2EFDA"]174[/TD]
[TD="bgcolor: #E2EFDA"]22+174=196[/TD]
[TD="bgcolor: #E2EFDA"]11+196=207[/TD]
[TD="bgcolor: #E2EFDA"]12+207=219[/TD]
[TD="bgcolor: #E2EFDA"]219[/TD]
[TD="bgcolor: #E2EFDA"]19+219=238[/TD]
[TD="bgcolor: #E2EFDA"]11+238=249[/TD]
[TD="bgcolor: #E2EFDA"]7+249=256[/TD]
[TD="bgcolor: #E2EFDA"]37+256=293[/TD]
[/TR]
</tbody>[/TABLE]

 

[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. )