To sumproduct or to sumif or to vlookup or to neither, that's the question

[almostanexpert]

Here is my problem. I have a worksheet that contains a set of calendar values (01-12) these values will need to be matched to another worksheet that will return a percentage. Aha vlookup right! Well not exactly. The calendar values in the percentage worksheet will sometimes repeat so I need to match these calendar values with a function value from the calendar worksheet. I am needing a formula that says something like this, lookup calendar value in the percentage worksheet and if there is no repeat of the calendar value then get the percentage and return this value, if the calendar value is repeated then match the function value and return this percentage, if there is no calendar the return zero.

Calendar Worksheet Percentage Worksheet

Cal Func Pct Cal Func Percentage
1 05 13 01 75%
2 12 02 3%
3 04 11 03 6%
4 04 13 04 20%
5 12 04 11 36%
6 05 23 05 56%
7 01 12 05 13 8%
8 05 13 05 23 15%
9 01 21 06 13 12%

Ans.
1. 8%
2. 0
3. 36%
4. 20%
5. 0
6. 15%
7. 75%
8. 8%
9. 75%

 

[almostanexpert]

Excel Workbook
	F	R	AB	AC	AD	AE
17		12				0
18	10	11				0
19	10	11				0
20	17	12				0
21	10	11				0
22	10	11				0
23	10	11				0
24	10	11				0
25	10	11				0
26	10	11				0
27	10	11				0
28	10	11				0
29	10	11				0
30	10	11				0
31	10	11				0
32	17	11				0
33	17	12				0
34	17	12				0
35	10	11				0
36	10	11				0
37	10	11				0
38	17	12				0
39	17	12				0
40	17	12				0
41	10	11				0
42	10	11				0
Cal

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

I went a little lower and started the copy at row 17 because all the previous rows have no value in Column F or Cal.

Sorry AE is what we have been using as Column D

 

[markmzz]

Could you post a small example that is return 0 (zero)?

Markmzz

Look at your PM.

Markmzz

 

[almostanexpert]

Just sent it.

Thanks.

 

[markmzz]

Just sent it.

Thanks.

Look at your email.

Do same tests and tell me if the formula work.

Markmzz

 

[markmzz]

Marcelo,

Here is the new formula that I think about the problem:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>R</th><th>AE</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">Cal</td><td style="text-align: center;background-color: #C5D9F1;;">Func</td><td style="text-align: center;background-color: #C5D9F1;;">Percentage</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">11</td><td style="text-align: center;;">11</td><td style="text-align: center;;">-22.98%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">22</td><td style="text-align: center;;">61</td><td style="text-align: center;;">130.69%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">11</td><td style="text-align: center;;">0.00%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">12</td><td style="text-align: center;;">31</td><td style="text-align: center;;">-16.98%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">12</td><td style="text-align: center;;">23</td><td style="text-align: center;;">82.98%</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">23</td><td style="text-align: center;;">0.00%</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">10</td><td style="text-align: center;;">12</td><td style="text-align: center;;">78.69%</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">18</td><td style="text-align: center;;">13</td><td style="text-align: center;;">5.19%</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">27</td><td style="text-align: center;;">21</td><td style="text-align: center;;">3.55%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style=";">********</td></tr></tbody></table><p style="width:1.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Cal</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AE2</th><td style="text-align:left">{=IF(<font color="Blue">ISERR(<font color="Red">F2*1</font>),0,SUM(<font color="Red">(<font color="Green">'Pct by Cal'!$A$2:$A$23=(<font color="Purple">F2*1</font>)</font>)*(<font color="Green">'Pct by Cal'!$D$2:$D$23</font>)*(<font color="Green">'Pct by Cal'!$C$2:$C$23=IF(<font color="Purple">COUNTIFS(<font color="Teal">'Pct by Cal'!$A$2:$A$23,(<font color="#FF00FF">F2*1</font>),'Pct by Cal'!$C$2:$C$23,(<font color="#FF00FF">R2*1</font>)</font>)>0,(<font color="Teal">R2*1</font>),""</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Note1: the data of columns F and R are text and not number.


Note2: if you have another formula, post it.

Markmzz

 

[almostanexpert]

REMARKABLE! Problem Solved!!

No doubt the devil is in the details. So far I have spot check my results and they are all coming out right on the money. I do hope that this problem help stimulate Mark and Marcelo's Excel prowess.

Many thanks Mark and thank you Marcelo for not giving up and your continued support.

 

[Marcelo Branco]

REMARKABLE! Problem Solved!!

Great!

Tks for the feedback

M.