Mass Formula with IF & LOOKUP

F

Finalmakerr

New Member
Joined
Aug 5, 2011
Messages
36
Hello,

Formula: =IF(LOOKUP("$",G10,F10),F10*$K$3,IF(LOOKUP("€",G10,F10),F10*$K$4,IF(LOOKUP("₪",G10,F10),F10,"")))

Issue :(: Only the first "IF" is working properly while the rest result in N/A#

Formula goal: Currency check and exchange


Further details:

CELL G ($,€,₪) = CURRENCY TYPE
CELL F = PRICE
CELL K = CURRENCY RATE


Thanks in advance,

Gal
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Try this, adapted to suit your cells

=IF(G10="$",F10*K3,IF(G10="€",F10*K4,IF(G10="₪",F10,"")))
 
Upvote 0
Hey Marcol,

Thanks for your quick answer. I found that writing an IF formula with logical test =symbol is possible only by writing it directly and that's what you just did. For example when i try to do that using the function arguments (build-in wizard) i receive an invalid error.

I'd like to know how to make a similar formula as the follow:


CURRENCY RATES: $ = 3.5 € = 5

<TABLE style="WIDTH: 162pt; BORDER-COLLAPSE: collapse" dir=rtl border=0 cellSpacing=0 cellPadding=0 width=216><COLGROUP><COL style="WIDTH: 54pt" span=3 width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=ltr class=xl72 height=19 width=72>PRICE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=ltr class=xl72 width=72>CURRENCY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl width=72></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=rtl class=xl69 height=19 align=right>1.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl class=xl71>₪</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=rtl class=xl70 height=19 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl class=xl71>€</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=rtl class=xl70 height=19 align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl class=xl71>$</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl height=19></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=rtl class=xl70 height=19 align=left>?</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=rtl class=xl71>₪</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" dir=ltr class=xl71>TOTAL:</TD></TR></TBODY></TABLE>


How do i get a single formula in the bottom that will check,exchange the currency and sum the cells above?
 
Upvote 0
With your table in B1:C4 and currency rates in E1:F3

In C6
=SUMIF(B2:B4,"₪",C2:C4)+(SUMIF(B2:B4,"$",C2:C4)*F2)+(SUMIF(B2:B4,"€",C2:C4)*F3)


<TABLE style="WIDTH: 307pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=410><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" span=2 width=76><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=51></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=76 align=left>CURRENCY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=76 align=left>PRICE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=79 align=left>CURRENCY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=left>RATE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=left>₪</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=left>$</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=left>€</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=left>€</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=left>$</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=left>TOTAL:

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2816250 align=left>₪</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>21.5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>
 
Upvote 0
Okay, just installed Exel Jeannie, thought I'd give it a try by repeating the last post.

It dosn't seem to like the Hebrew character for shekels (₪) and has replaced it with ? in both the table and the formula


Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 51px"><COL style="WIDTH: 76px"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 79px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD style="BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">CURRENCY</TD><TD style="BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">PRICE</TD><TD></TD><TD style="BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">CURRENCY</TD><TD style="BACKGROUND-COLOR: #000000; COLOR: #ffffff; FONT-WEIGHT: bold">RATE</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD>?</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>$</TD><TD style="TEXT-ALIGN: right">3.5</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD>€</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD>€</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD>$</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>TOTAL:</TD><TD>?</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc00; FONT-WEIGHT: bold">21.5</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C6</TD><TD>=SUMIF(B2:B4,"?",C2:C4)+(SUMIF(B2:B4,"$",C2:C4)*F2)+SUMIF(B2:B4,"€",C2:C4)*F3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
Hey Marcol,

Thank you for your help.

I have another issue related to SUMIF command as i have to figure out how to do SUMIF for a serveral unfollowing cells (see cell I52).



<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 72px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">€</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">$</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">€</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">€</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">5.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">1.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">1.20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-SIZE: 12pt; FONT-WEIGHT: bold">€</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 12pt">€</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">1.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">2.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">1.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-WEIGHT: bold">0.80</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 12pt; FONT-WEIGHT: bold">€</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">1.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">5.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">5.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">$</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">5.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">2.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">30.50</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 12pt; FONT-WEIGHT: bold">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">43</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">44</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">45</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">46</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">1.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">47</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">48</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">49</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt">¤</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">50</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-WEIGHT: bold">0.20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-SIZE: 12pt; FONT-WEIGHT: bold">€</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">51</TD><TD> </TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">52</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold" rowSpan=2>#######</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 13pt; FONT-WEIGHT: bold" rowSpan=2>€</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">53</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>I23</TD><TD>=IF($J$23="¤",(SUMIF($J8:$J22,"¤",I8:I21)+(SUMIF($J8:$J22,"$",I8:I21)*$K$3)+(SUMIF($J8:$J22,"€",I8:I21)*$K$4)),IF($J$23="$",(SUMIF($J8:$J22,"¤",I8:I21)/$K$3)+(SUMIF($J8:$J22,"$",I8:I21))+(SUMIF($J8:$J22,"€",I8:I21)*($K$4-$K$3)),IF($J$23="€",SUMIF($J8:$J22,"¤",I8:I21)/$K$4)+(SUMIF($J8:$J22,"$",I8:I21)/($K$4-$K$3))+(SUMIF($J8:$J22,"€",I8:I21))))</TD></TR><TR><TD>I30</TD><TD>=IF($J30="¤",(SUMIF($J24:$J29,"¤",I24:I28)+(SUMIF($J24:$J29,"$",I24:I28)*$K$3)+(SUMIF($J24:$J29,"€",I24:I28)*$K$4)),IF($J30="$",(SUMIF($J24:$J29,"¤",I24:I28)/$K$3)+(SUMIF($J24:$J29,"$",I24:I28))+(SUMIF($J24:$J29,"€",I24:I28)*($K$4-$K$3)),IF($J30="€",SUMIF($J24:$J29,"¤",I24:I28)/$K$4)+(SUMIF($J24:$J29,"$",I24:I28)/($K$4-$K$3))+(SUMIF($J24:$J29,"€",I24:I28))))</TD></TR><TR><TD>I40</TD><TD>=IF($J40="¤",(SUMIF($J31:$J39,"¤",I31:I39)+(SUMIF($J31:$J39,"$",I31:I39)*$K$3)+(SUMIF($J31:$J39,"€",I31:I39)*$K$4)),IF($J40="$",(SUMIF($J31:$J39,"¤",I31:I39)/$K$3)+(SUMIF($J31:$J39,"$",I31:I39))+(SUMIF($J31:$J39,"€",I31:I39)*($K$4-$K$3)),IF($J40="€",SUMIF($J31:$J39,"¤",I31:I39)/$K$4)+(SUMIF($J31:$J39,"$",I31:I39)/($K$4-$K$3))+(SUMIF($J31:$J39,"€",I31:I39))))</TD></TR><TR><TD>I50</TD><TD>=IF($J50="¤",(SUMIF($J41:$J49,"¤",I41:I49)+(SUMIF($J41:$J49,"$",I41:I49)*$K$3)+(SUMIF($J41:$J49,"€",I41:I49)*$K$4)),IF($J50="$",(SUMIF($J41:$J49,"¤",I41:I49)/$K$3)+(SUMIF($J41:$J49,"$",I41:I49))+(SUMIF($J41:$J49,"€",I41:I49)*($K$4-$K$3)),IF($J50="€",SUMIF($J41:$J49,"¤",I41:I49)/$K$4)+(SUMIF($J41:$J49,"$",I41:I49)/($K$4-$K$3))+(SUMIF($J41:$J49,"€",I41:I49))))</TD></TR><TR><TD>I52</TD><TD>=IF($J52="¤",(SUMIF(($J23,$J30,$J40),"¤",(I23,I30,I40))+(SUMIF(($J23,$J30,$J40),"$",(I23,I30,I40))*$K$3)+(SUMIF(($J23,$J30,$J40),"€",(I23,I30,I40))*$K$4)),IF($J52="$",(SUMIF(($J23,$J30,$J40),"¤",(I23,I30,I40))/$K$3)+(SUMIF(($J23,$J30,$J40),"$",(I23,I30,I40)))+(SUMIF(($J23,$J30,$J40),"€",(I23,I30,I40))*($K$4-$K$3)),IF($J52="€",SUMIF(($J23,$J30,$J40),"¤",(I23,I30,I40))/$K$4)+(SUMIF(($J23,$J30,$J40),"$",(I23,I30,I40))/($K$4-$K$3))+(SUMIF(($J23,$J30,$J40),"€",(I23,I30,I40)))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Thanks a lot,

Gal
 
Upvote 0
I would move the subtotals to a new column, say Column K and the currency symbol to Column L
Then, as you haven't shown any way to divide the groups, in say K24
Code: 
=(SUMIF(J2:J16,"₪",I2:I16)+(SUMIF(J2:J16,"$",I2:I16)*$F$2)+(SUMIF(J2:J16,"€",I2:I16)*$F$3))/IF(L16="€",$F$3,IF(L16="$",$F$2,1))
Then in L24 put the required symbol

Do similar for each subtotal you need.

Then in L1
Code: 
=(SUMPRODUCT(K2:K100,--(L2:L100="₪"))+(SUMPRODUCT(K2:K100,--(L2:L100="$")))*$F$2+(SUMPRODUCT(K2:K100,--(L2:L100="€")))*$F$3)/IF(M1="$",$F$2,IF(M1="€",$F$3,1))
and in M1 put the required currency symbol.


With the following remember to change ? to ₪

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 89px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 86px"><COL style="WIDTH: 86px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">CURRENCY</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">RATE</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">PRICE</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">CURRENCY</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">Total ></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc00; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">8.30</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-WEIGHT: bold">€</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana">$</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3.5</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">€</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">5</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">€</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">$</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">€</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">€</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">1.20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">€</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">€</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">0.80</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">€</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">$</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">30.50</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">?</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana">0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-SIZE: 12pt">?</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">0.20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-FAMILY: Verdana; FONT-WEIGHT: bold">€</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L1</TD><TD>=(SUMPRODUCT(K2:K100,--(L2:L100="?"))+(SUMPRODUCT(K2:K100,--(L2:L100="$")))*$F$2+(SUMPRODUCT(K2:K100,--(L2:L100="€")))*$F$3)/IF(M1="$",$F$2,IF(M1="€",$F$3,1))</TD></TR><TR><TD>K16</TD><TD>=(SUMIF(J2:J16,"?",I2:I16)+(SUMIF(J2:J16,"$",I2:I16)*$F$2)+(SUMIF(J2:J16,"€",I2:I16)*$F$3))/IF(L16="€",$F$3,IF(L16="$",$F$2,1))</TD></TR><TR><TD>K22</TD><TD>=(SUMIF(J17:J22,"?",I17:I22)+(SUMIF(J17:J22,"$",I17:I22)*$F$2)+(SUMIF(J17:J22,"€",I17:I22)*$F$3))/IF(L22="€",$F$3,IF(L22="$",$F$2,1))</TD></TR><TR><TD>K31</TD><TD>=(SUMIF(J23:J31,"?",I23:I31)+(SUMIF(J23:J31,"$",I23:I31)*$F$2)+(SUMIF(J23:J31,"€",I23:I31)*$F$3))/IF(L31="€",$F$3,IF(L31="$",$F$2,1))</TD></TR><TR><TD>K40</TD><TD>=(SUMIF(J32:J40,"?",I32:I40)+(SUMIF(J32:J40,"$",I32:I40)*$F$2)+(SUMIF(J32:J40,"€",I32:I40)*$F$3))/IF(L40="€",$F$3,IF(L40="$",$F$2,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
Hey Marcol,

Thanks for your help again :).

I have to keep the total at the same column since i have 4 columns with different prices -one after another. I am still trying to find a way to have a formula with CELL+CELL+CELL in SUMIF, that's instead to having CELL:CELL (: = till) in SUMIF.


P.S: "Excel Jenie" tend to mess with the sheet direction when i copy-paste and i think it's cause my sheet is from Right-to-Left.
 
Upvote 0
With the sample you gave in Post #6
In I17
Code: 
=(SUMIF(J2:J16,"₪",I2:I16)+(SUMIF(J2:J16,"$",I2:I16)*$F$2)+(SUMIF(J2:J16,"€",I2:I16)*$F$3))/IF(J17="€",$F$3,IF(J17="$",$F$2,1))


In I24
Code: 
=(SUMIF(J18:J23,"₪",I18:I23)+(SUMIF(J18:J23,"$",I18:I23)*$F$2)+(SUMIF(J18:J23,"€",I18:I23)*$F$3))/IF(J24="€",$F$3,IF(J24="$",$F$2,1))


In I34
Code: 
=(SUMIF(J25:J33,"₪",I25:I33)+(SUMIF(J25:J33,"$",I25:I33)*$F$2)+(SUMIF(J25:J33,"€",I25:I33)*$F$3))/IF(J34="€",$F$3,IF(J34="$",$F$2,1))


In I44
Code: 
=(SUMIF(J35:J43,"₪",I35:I43)+(SUMIF(J35:J43,"$",I35:I43)*$F$2)+(SUMIF(J35:J43,"€",I35:I43)*$F$3))/IF(J44="€",$F$3,IF(J44="$",$F$2,1))

In I46
Code: 
=(SUMPRODUCT(I2:I45,--(J2:J45="₪"))+(SUMPRODUCT(I2:I45,--(J2:J45="$")))*$F$2+(SUMPRODUCT(I2:I45,--(J2:J45="€")))*$F$3)/IF(O1="$",$F$2,IF(O1="€",$F$3,1))/2


Because you have given no way to tell when a group could start or finish, I think that's as good as you'll get.

Maybe, for examlpe, a date range or invoice/bill numbers would help to give a more general formula for the sub-totals.

Why merge the cells I46:I47 & J46:J47?
Don't, just increase the cell height and font size if you need to make the result more obvious.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Lookup formula that doesn't have a fixed array (partial match)
Replies
3
Views
368
arthurbr
arthurbr
K
  • Question Question
Populate With Criteria
Replies
3
Views
83
Peter_SSs
Peter_SSs
D
  • Solved
Chart Help Please
Replies
1
Views
109
dmj120
D
Sean15
  • Solved
Rewrite formula so 2nd, 3rd…occurrence of lookup value return blank cell
Replies
6
Views
253
Peter_SSs
Peter_SSs
J
  • Solved
Lookup formula to determine activity use
Replies
4
Views
176
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,597
Messages
6,179,813
Members
452,945
Latest member
Bib195

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top