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]

You want to see how I am using the formula? Please excuse my misunderstanding.

 

[markmzz]

You want to see how I am using the formula? Please excuse my misunderstanding.

Sorry, but I'm trying to find why the formulas are not working.

You test the formula with the data in my post #17?

If yes, then all the column D return 0 (zero)?


Markmzz

 

[almostanexpert]

Yes this is true I tried both formulas and they are both returning 0 including the one you posted in message #17. Here is how I am applying the formula. It has been slightly modified to reference the true reference cells.

=SUM(('Pct by Cal'!$A$2:$A$23=F2)*('Pct by Cal'!$D$2:$D$23)*('Pct by Cal'!$C$2:$C$23=IF(COUNTIFS('Pct by Cal'!$A$2:$A$23|F2|'Pct by Cal'!$C$2:$C$23|R2)>0|R2|"")))

Please note that this formula does have the CSE brackets around it {}.

 

[Marcelo Branco]

Yes this is true I tried both formulas and they are both returning 0 including the one you posted in message #17. Here is how I am applying the formula. It has been slightly modified to reference the true reference cells.

=SUM(('Pct by Cal'!$A$2:$A$23=F2)*('Pct by Cal'!$D$2:$D$23)*('Pct by Cal'!$C$2:$C$23=IF(COUNTIFS('Pct by Cal'!$A$2:$A$23|F2|'Pct by Cal'!$C$2:$C$23|R2)>0|R2|"")))

Please note that this formula does have the CSE brackets around it {}.

Hi,

Could you post your data in Pct by Cal?

To do this:
Select the range containing your data including headers (A1:D23 i suppose)
Put borders
Copy (Ctrl+C)
Paste (Ctrl+V) in the forum reply page

By the way, have you tried my formula in #16? I made several tests and it worked fine

M.

 

[markmzz]

Yes this is true I tried both formulas and they are both returning 0 including the one you posted in message #17. Here is how I am applying the formula. It has been slightly modified to reference the true reference cells.

=SUM(('Pct by Cal'!$A$2:$A$23=F2)*('Pct by Cal'!$D$2:$D$23)*('Pct by Cal'!$C$2:$C$23=IF(COUNTIFS('Pct by Cal'!$A$2:$A$23|F2|'Pct by Cal'!$C$2:$C$23|R2)>0|R2|"")))

Please note that this formula does have the CSE brackets around it {}.

Look at this:

=SUM(('Pct by Cal'!$A$2:$A$23=F2)*('Pct by Cal'!$D$2:$D$23)*('Pct by Cal'!$C$2:$C$23=IF(COUNTIFS('Pct by Cal'!$A$2:$A$23|F2|'Pct by Cal'!$C$2:$C$23|R2)>0|R2|"")))

Replace R2 for C2 and F2 for A2 and try again.

Here is a example like yours:

<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 /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</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;;"># Days</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;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">13</td><td style="text-align: center;;">8%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;">36%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">13</td><td style="text-align: center;;">20%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0%</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">23</td><td style="text-align: center;;">15%</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">12</td><td style="text-align: center;;">75%</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">13</td><td style="text-align: center;;">8%</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">21</td><td style="text-align: center;;">75%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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="text-align: center;;">******</td><td style="text-align: center;;">******</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">D2</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">'Pct by Cal'!$A$2:$A$23=A2</font>)*(<font color="Red">'Pct by Cal'!$D$2:$D$23</font>)*(<font color="Red">'Pct by Cal'!$C$2:$C$23=IF(<font color="Green">COUNTIFS(<font color="Purple">'Pct by Cal'!$A$2:$A$23,A2,'Pct by Cal'!$C$2:$C$23,C2</font>)>0,C2,""</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 /><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 /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</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;;"># Days</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;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">75%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">6%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">20%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;">36%</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">56%</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">13</td><td style="text-align: center;;">8%</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">23</td><td style="text-align: center;;">15%</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;">13</td><td style="text-align: center;;">12%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">75%</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;">16</td><td style="text-align: center;;">3%</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">9</td><td style="text-align: right;;"></td><td style="text-align: center;;">17</td><td style="text-align: center;;">6%</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">20%</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">11</td><td style="text-align: right;;"></td><td style="text-align: center;;">16</td><td style="text-align: center;;">36%</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;">17</td><td style="text-align: center;;">56%</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8%</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">15%</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">9</td><td style="text-align: right;;"></td><td style="text-align: center;;">16</td><td style="text-align: center;;">12%</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;">17</td><td style="text-align: center;;">15%</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">12%</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">20%</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">36%</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:6em;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">Pct by Cal</p><br /><br />

Markmzz

 

[almostanexpert]

Excel Workbook
	A	B	C	D
1	Calendar	# Days	Func	Percentage
2	10	183		78.69%
3	11	187	11	-22.98%
4	11	187		77.02%
5	12	188	31	-16.98%
6	12	188	23	82.98%
7	13	190	35	7.39%
8	14	190		-11.55%
9	15	192		-6.25%
10	16	193	11	7.77%
11	16	193	13	7.77%
12	17	193	12	105.70%
13	18	194		5.19%
14	19	197		3.59%
15	20	198	23	15.15%
16	20	198	31	15.15%
17	22	202		30.66%
18	22	202	61	130.69%
19	23	210		71.43%
20	24	210		37.14%
21	26	215		95.31%
22	25	225		8.00%
23	27	230		3.55%
Pct by Cal

Excel 2007

Wow this is too cool, I had not used the HTMLMaker. Here it is, Pct by Cal worksheet.

 

[almostanexpert]

Hello Mark,

I don't mean to be difficult but F2 and R2 are the actual cell addresses (in Cal worksheet) that need to be reference where Cal and Func are located respectfully.

I appreciate your continued support.

 

[markmzz]

Hello Mark,

I don't mean to be difficult but F2 and R2 are the actual cell addresses (in Cal worksheet) that need to be reference where Cal and Func are located respectfully.

I appreciate your continued support.

Hello Almostanexpert,

Here is a example with the actual cell addresses (F2 and R2) and with your data of sheet Pct by Cal:

<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>S</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: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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="text-align: center;;">******</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">S2</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">'Pct by Cal'!$A$2:$A$23=F2</font>)*(<font color="Red">'Pct by Cal'!$D$2:$D$23</font>)*(<font color="Red">'Pct by Cal'!$C$2:$C$23=IF(<font color="Green">COUNTIFS(<font color="Purple">'Pct by Cal'!$A$2:$A$23,F2,'Pct by Cal'!$C$2:$C$23,R2</font>)>0,R2,""</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 />
Could you send me a example of your workbook for tests?

Markmzz

 

[Marcelo Branco]

Excel Workbook
	A	B	C	D
1	Calendar	# Days	Func	Percentage
2	10	183		78.69%
3	11	187	11	-22.98%
4	11	187		77.02%
5	12	188	31	-16.98%
6	12	188	23	82.98%
7	13	190	35	7.39%
8	14	190		-11.55%
9	15	192		-6.25%
10	16	193	11	7.77%
11	16	193	13	7.77%
12	17	193	12	105.70%
13	18	194		5.19%
14	19	197		3.59%
15	20	198	23	15.15%
16	20	198	31	15.15%
17	22	202		30.66%
18	22	202	61	130.69%
19	23	210		71.43%
20	24	210		37.14%
21	26	215		95.31%
22	25	225		8.00%
23	27	230		3.55%
Pct by Cal

Excel 2007



Wow this is too cool, I had not used the HTMLMaker. Here it is, Pct by Cal worksheet.

yes, its cool.

If possible do the same with the other worksheet where you need to get the results.

M.

 

[almostanexpert]

How do I do this? Is there an email address I can sent it to you and only you?