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%

 

[markmzz]

Hi Markmzz,

I think you dont need to include the 13 in the data using this

=IF(COUNTIFS($E$2:$E$10,A2,$F$2:$F$10,B2)>0,SUMPRODUCT(--($E$2:$E$10=A2),--($F$2:$F$10=B2),$G$2:$G$10),SUMPRODUCT(--($E$2:$E$10=A2),--($F$2:$F$10=""),$G$2:$G$10))

M.

Hi Marcelo,

As the table submitted by the user can generate questions, I thought that was a typo (missing type the 13 in F5).

Look at this:

"if the calendar value is repeated then match the function value and return this percentage"

Anyway, thanks for the tip.

Markmzz

 

[almostanexpert]

The sheet name = Pct by Cal

Data-Range = A2:D23

As far as the separator, I am using American version of Excel 2007 and if I save the workbook as a .xlsx I need to use pipe "|" or I get error messages when I press enter after typing a formula, now if I use the compatibility save and save it as a .xls then I can use only commas "," as s separator.

 

[Marcelo Branco]

The sheet name = Pct by Cal

Data-Range = A2:D23

Accordingly to your original post i thought your table had only 3 columns, ie:

Cal Func Percentage

but now you are saying that there are 4 columns A B C D

Could you, please, clarify?

M.
ps: are your headers in row 2 and data beginning in row 3?

 

[markmzz]

Almostanexpert,

Try this to post your example (use borders):

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Product</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Price</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>109</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>$100.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>203</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>$200.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>305</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>$400.00 </TD></TR></TBODY></TABLE>

Markmzz

 

[almostanexpert]

My apologies, there are four columns I did not include it as it does not need to be evaluated. The column heads are Cal, # Days, Func and Percentage. I hope this is not an inconvenience.

 

[Marcelo Branco]

My apologies, there are four columns I did not include it as it does not need to be evaluated. The column heads are Cal, # Days, Func and Percentage. I hope this is not an inconvenience.

No Problem.

Assuming:
Sheet Name = Pct by Cal
Headers in row 1
data beginning at row 2, ending in row 23

Columns of interest
<TABLE style="WIDTH: 219pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=292><COLGROUP><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" width=23><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 17pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=23> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=77>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cal</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Function</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Percentage</TD></TR></TBODY></TABLE>

Separator = | (pipe)

Try this in C2 of the other sheet

=IF(COUNTIFS('Pct by Cal'!$A$2:$A$23|A2|'Pct by Cal'!$C$2:$C$23|B2)>0|SUMPRODUCT(--('Pct by Cal'!$A$2:$A$23=A2)|--('Pct by Cal'!$C$2:$C$23=B2)|'Pct by Cal'!$D$2:$D$23)|SUMPRODUCT(--('Pct by Cal'!$A$2:$A$23=A2)|--('Pct by Cal'!$C$2:$C$23="")|'Pct by Cal'!$D$2:$D$23))

copy down

HTH

M.

 

[markmzz]

Another formula (array formula - use Ctrl+Shift+Enter and not only Enter):

<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 /><col /><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><th>E</th><th>F</th><th>G</th><th>H</th><th>I</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><td style="text-align: center;background-color: #C5D9F1;;"></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><td style="text-align: right;;"></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;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0%</td><td style="text-align: right;;"></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;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;">36%</td><td style="text-align: right;;"></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;;">13</td><td style="text-align: center;;">20%</td><td style="text-align: right;;"></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;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0%</td><td style="text-align: right;;"></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;;">23</td><td style="text-align: center;;">15%</td><td style="text-align: right;;"></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;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">12</td><td style="text-align: center;;">75%</td><td style="text-align: right;;"></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;;">13</td><td style="text-align: center;;">8%</td><td style="text-align: right;;"></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;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">21</td><td style="text-align: center;;">75%</td><td style="text-align: right;;"></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;;"></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><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><td style="text-align: center;;">******</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:4.2em;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">Sheet21</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">$F$2:$F$10=A2</font>)*(<font color="Red">$I$2:$I$10</font>)*(<font color="Red">$H$2:$H$10=IF(<font color="Green">COUNTIFS(<font color="Purple">$F$2:$F$10,A2,$H$2:$H$10,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 />
If I understand correctly, this is the formula in your format:

=SUM(($F$2:$F$23=A2)*($I$2:$I$23)*($H$2:$H$23=IF(COUNTIFS($F$2:$F$23|A2|$H$2:$H$23|C2)>0|C2|"")))

Markmzz

 

[markmzz]

No Problem.
Try this in C2 of the other sheet

=IF(COUNTIFS('Pct by Cal'!$A$2:$A$23|A2|'Pct by Cal'!$C$2:$C$23|B2)>0|SUMPRODUCT(--('Pct by Cal'!$A$2:$A$23=A2)|--('Pct by Cal'!$C$2:$C$23=B2)|'Pct by Cal'!$D$2:$D$23)|SUMPRODUCT(--('Pct by Cal'!$A$2:$A$23=A2)|--('Pct by Cal'!$C$2:$C$23="")|'Pct by Cal'!$D$2:$D$23))

copy down

HTH

M.

Here is my formula (array formula - entered with Ctrl+Shift+Enter) in the format post by Marcelo:

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

Markmzz

 

[almostanexpert]

Hello Mark and Marcelo, I am reporting my findings on the formula that you two have so graciously provided. Unfortunately I am still not getting the results needed. One good thing is that it is not giving me #N/A but 0. I was thinking about explaining my logic to see if it would explain what I am trying to achieve. Yes I am entering the formula as an array Ctrl+Shift+Enter

Process,
In cell D2 enter a formula that states; lookup cell A2 (Cal) and see if there is a corresponding percentage from column I and return this value to D2 (Percentage). If there is no value in A2 then return a zero. If there is more than one Cal (A2) then use C2 (Func) to tell them apart, match both A2 & C2 to F2 & H2 and provide this value from column I.

I hope this helps and thank you again. I do believe you are moving in the right direction.

 

[markmzz]

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

Markmzz